CASE is used in providing if-then-else type of logic to SQL.
There are two formats:
The first is a Simple CASE expression, where we compare an expression to static values.
The second is a Searched CASE expression, where we compare an expression to one or more logical conditions.
Simple CASE Expression Syntax
The syntax for a simple CASE expression is:
SELECT CASE ("column_name")
WHEN "value1" THEN "result1"
WHEN "value2" THEN "result2"
...
[ELSE "resultN"]
END
FROM "table_name";
The ELSE statement is optional in a CASE expression.
When one is not defined, and no matches are made, NULL is returned.
If an ELSE is defined, and no matches are made, then “otherResult” is returned.
The ELSE clause is a great way to catch bad or unexpected data values, and return a result other than NULL.
Simple CASE Expression Example
We use the following table for our example.
Table Store_Information
Store_Name
Sales
Txn_Date
Alabama
1500
March-06-2017
Ohio
250
March -08-2017
Texas
300
March-09-2017
Wyoming
700
March-09-2017
To multiply the sales amount from ' Alabama ' by 2 and the sales amount from ' Ohio ' by 1.5 while keeping the sales amount for other stores the same, we would use the following SQL statement using CASE:
SELECT Store_Name, CASE Store_Name
WHEN ' Alabama ' THEN Sales * 2
WHEN ' Ohio ' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Txn_Date
FROM Store_Information;
"New Sales" is the name given to the column with the CASE statement.
This is an example of a simple CASE expression, because the conditions listed, ' Alabama ' and ' Ohio ', are static values.
Result:
Store_Name
New Sales
Txn_Date
Alabama
3000
Jan-05-2017
Ohio
375
Jan-07-2017
Texas
300
Jan-08-2017
Wyoming
700
Jan-08-2017
Searched CASE Expression Syntax
The syntax for a searched CASE expression is:
SELECT CASE
WHEN "condition1" THEN "result1"
WHEN "condition2" THEN "result2"
...
[ELSE "resultN"]
END
FROM "table_name";
The ELSE clause is optional. "Condition" can consist of one or more logical statements.
Searched CASE Expression Example
We use the same Store_Information above. If we want to define the status of a store's sale based on the following rules:
If Sales >= 1,000, it's a "Good Day"
If Sales >= 500 and < 1,000, it's an "OK Day"
If Sales < 500, it's a "Bad Day"
We can use the following searched CASE expression:
SELECT Store_Name, Txn_Date, CASE
WHEN Sales >= 1000 THEN 'Good Day'
WHEN Sales >= 500 THEN 'OK Day'
ELSE 'Bad Day'
END
"Sales Status"
FROM Store_Information;
Result:
Store_Name
Txn_Date
Sales Status
Alabama
Jan-05-2017
Good Day
Ohio
Jan-07-2017
Bad Day
Texas
Jan-08-2017
Bad Day
Wyoming
Jan-08-2017
OK Day
Note that a simple CASE expression is a special case of a searched CASE expression. As an example, the following two CASE expressions are identical:
Simple CASE Expression:
SELECT Store_Name, CASE Store_Name
WHEN 'Alabama' THEN Sales * 2
WHEN 'Ohio' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Txn_Date
FROM Store_Information;
Searched CASE Expression:
SELECT Store_Name, CASE
WHEN Store_Name = ' Alabama ' THEN Sales * 2
WHEN Store_Name = ' Ohio ' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Txn_Date
FROM Store_Information;
This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf
Related Searches to Case Statement in SQL
case statement in sql select queryhow to use case statement in sqlcase statement in sql server 2012case statement in sql server examplecase statement in sql server with examplecase statement in sql with examplecase statement in sql query examplecase statement in sql queryupdate with case statement in sql servercase statement in sql where clausehow to use case statement in sql serverhow to write case statement in sqlusing case statement in sqlupdate with case statement in sqlcase statement in sql selectmultiple case statement in sql serverhow to write a case statement in sqlupdate case statement in sqlsql case statement w3schoolssql if then elsemultiple case when sqlcase statement in sql oraclesql case when nullsql case statement in where clausecase when mysqlnested case statement in sql
