Questions tagged [case]

In SQL, the CASE expression evaluates a list of conditions, returning the result for the first condition which evaluates to true. For programming languages' CASE questions, use the [switch-statement] tag instead.

The CASE expression is an SQL construction used to return different values depending on specified conditions. There are two forms of the CASE expression. The first is the simple CASE:

CASE search_expr
WHEN compare_expr1 THEN result_expr1
WHEN compare_expr2 THEN result_expr2
ELSE else_expr
END

This will compare search_expr to each compare_expr in order until it finds a condition where search_expr = compar_expr, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

The second form of CASE expression is the searched CASE:

CASE 
WHEN boolean_expr1 THEN result_expr1
WHEN boolean_expr2 THEN result_expr2
ELSE else_expr
END

This will test boolean_expr in order until it finds a condition where the boolean_expr is true, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

There are two short forms for special CASE expressions; COALESCE and NULLIF.

COALESCE(x1, x2, ..., xn) 

is equivalent to:

CASE WHEN x1 IS NOT NULL THEN x1 
     WHEN x2 IS NOT NULL THEN x2
     ...
     ELSE xn
END 

I.e. the COALESCE expression returns the value of the first non-null operand, found by working from left to right, or null if all the operands equal null.

NULLIF(x1, x2) 

is equivalent to

CASE WHEN x1 = x2 THEN NULL ELSE x1 END

I.e. if the operands are equal, NULLIF returns null, otherwise the value of the first operand.

8090 questions
1
vote
1 answer

Case Statement, Like operator in SQL

I have multiple rows in the table (as in the image). I need to make a new column from specific keywords extracted from the strings. Title Output Ad - LA - [Ke] [NC] [W] Test for IPHIndi W [MWeb] My Ads Center (Batch 3) Part 2 Mweb Ad -…
1
vote
1 answer

How to compare all values from previous hour to current hour using SQL

I have this table that I need to compare the values from the current hour with the previous hour for each ID. id value time A 1 3pm B 2 3pm C 3 3pm A 3 2pm B 2 2pm C 1 2pm A 3 1pm B 2 1pm C 2 1pm If the values from the…
1
vote
1 answer

Should I use CASE WHEN or IIF? Are subqueries a possible part of the solution?

I'm using My SQL (latest version) and I have the following table: CREATE TABLE COURSES ( IDCOURSE INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (30) NOT NULL, HOURS INT, PRICE FLOAT (10,2) NOT NULL, ID_PREREQ INT ); ALTER TABLE COURSES ADD…
1
vote
1 answer

Substract the price using Case in Mysql

I have 3 tables: training_schedules, training_discounts, and agents. training_schedules: id, name, agent_id, price. training_discounts: id, agent_id, schedule_id, discount. agents: id, name I try to subtract the price from training_schedules table…
Ying
  • 1,282
  • 4
  • 19
  • 34
1
vote
1 answer

MYSQL sort date by future dates first in ASC and old dates then in DESC

I want to sort dates in MYSQL in a way such that, future dates will be sorted first by in ASC order and, then then old dates last in DESC order. Here is the query I used to do, but both date types (future and old) are sorted out in ASC order. How…
shayanmalinda
  • 1,925
  • 3
  • 12
  • 23
1
vote
1 answer

How to retrieve the required string in SQL having a variable length parameter

Here is my problem statement: I have single column table having the data like as : ROW-1>> 7302-2210177000-XXXX-XXXXXX-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-U-XXXXXXXXX-XXXXXX ROW-2>>…
1
vote
2 answers

ORA-00905: missing keyword while Using [CASE]) Clause in WHERE condition in Oracle SQL

I'm trying to run the two sets of queries on the same dataset with Both ([AND/OR] and [CASE]) approaches, where one [AND/OR] is working and the other [CASE] is not working and getting "ORA-00905: missing keyword" Condition is: For Columns [C1, C2,…
Rajesh
  • 562
  • 1
  • 10
  • 26
1
vote
1 answer

Date compare within Pivot

I am still working on getting the data out exactly as I want it and it is almost there - one last question. Here is my code: DECLARE @QA1 TABLE (SID varchar(7), FormID varchar(max), DateExam date, Present varchar(3)) INSERT INTO @QA1…
user918967
  • 2,049
  • 4
  • 28
  • 43
1
vote
4 answers

Pandas Case Statement Not Showing Correct Output

I am trying to write a case statement where if the column value = 50 THEN 50 ELSE 7 into a new column. When i do this case statement - there are certainly columns where the value is 50 but is still spitting out 7. df['wattage'] =…
Anna Chan
  • 11
  • 1
1
vote
1 answer

How to get the correct sum for two columns using case when

I am working on a program that will track a salespersons sold units, these units can be full deals (1) or half deals (0.5). What I need to do is find a way to get the SUM of the full deals and the half deals grouped by a salespersons ID. Here is the…
Ray C
  • 164
  • 8
1
vote
1 answer

c++) My calculator code runs but it doesn't do simple questions like 5+10?

*please help a newbie out. My code is a simple calculator code; it's supposed to handle +,-,/, operations. On hindsight, the code does run, but it doesn't do what it's supposed to do. If you input a question, it doesn't give an answer. For instance,…
Kim Minji
  • 11
  • 1
1
vote
1 answer

How do I select and join from a single table with 2 unique IDs based on one ID and concatenate the options?

I don't have much experience with SQL so forgive me if it is a simple answer... I am working on a personal project in Python and I am using sqlite3. I currently have a table created like this: CREATE TABLE IF NOT EXISTS table (id text, username…
zack komo
  • 45
  • 2
1
vote
3 answers

case statement in a where clause

I am trying to create a where statement that considers what day of the week it is and then filters according. For example, if the day I am pulling the data is a Wednesday, I only want to pull data for Monday - Wednesday of that week. If it is a…
1
vote
1 answer

SQL Server => How to use "case when" on multiple column to show a formula result based on a condition in the same line

I'm trying to provide an overall result of a formula ([CALCULATION]) and then in two extra columns ([NO_TENURE] & [TENURE]) the same calculation but using "CASE WHEN" to filter the information based on another column called [TENURE], everything in…
1
vote
1 answer

Convert char to date Then add to last date to get next date

I think the answer is a mix of using CASE expression, CONVERT/CAST, and string manipulation, but I couldn't get the syntax logic to make sense. I have the following: Account Open Date Renewal Date Duration 1111 2/23/1995 5/23/2018 12…
Cinji18
  • 619
  • 8
  • 22
1 2 3
99
100