Home » SQL CASE STATEMENT

SQL CASE STATEMENT

The CASE statement has the functionality of an IF-THEN-ELSE. It goes through conditions and returns a value when the first condition is met . So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.





If there is no ELSE part and no conditions are true, it returns NULL.

There are two different types of format that can be used in CASE statement.

The simple CASE statement expression: It evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.

The searched CASE expression: It evaluates a condition using a comparison operators ,if condition is satisfied, It returns an expression corresponding THEN in the output.

SYNTAX

--Simple case expression:

CASE input_expression 
WHEN when_expression THEN result_expression [ ...n ] 
[ ELSE else_result_expression ] 
END

--Searched CASE expression:

CASE WHEN Boolean_expression 
THEN result_expression [ ...n ] 
[ ELSE else_result_expression ] 
END

Input_expression
Is the expression that is evaluated when the simple CASE format is used.

WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used.  The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression
Is the expression that is returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.

ELSE else_result_expression
Is the expression that is returned if no comparison operation evaluates to TRUE. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression
Is the Boolean expression that is evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Lets Look at an example of CASE Statement in SQL Server.

First we create a sample table named ITEM_DETAILS and insert some records in table.

CREATE TABLE ITEM_DETAILS (ID INT NOT NULL, 
ITEM VARCHAR(50), 
ITEM_QTY INT, 
ITEM_PURCHASED_DATE DATETIME)

INSERT INTO ITEM_DETAILS (ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE)
VALUES (1, 'Scanner', 300, '2019-12-01 00:00:00.000'),
(2, 'Scanner', 250, '2019-12-10 00:00:00.000'),
(3, 'Scanner', 110, '2019-11-12 00:00:00.000'),
(4, 'Scanner', 110, '2019-09-19 00:00:00.000'),
(5, 'Scanner', 280, '2019-09-25 00:00:00.000'),
(6, 'Scanner', 180, '2019-08-15 00:00:00.000'),
(7, 'Scanner', 180, '2019-07-08 00:00:00.000'),
(8, 'Pen Drive', 360, '2019-12-15 00:00:00.000'),
(9, 'Pen Drive', 270, '2019-09-11 00:00:00.000'),
(10, 'Pen Drive', 210, '2019-08-22 00:00:00.000'),
(11, 'Pen Drive', 190, '2019-08-19 00:00:00.000'),
(12, 'Pen Drive', 480, '2019-07-15 00:00:00.000'),
(13, 'Pen Drive', 680, '2019-06-25 00:00:00.000'),
(14, 'Pen Drive', 120, '2019-06-18 00:00:00.000'),
(15, 'Hard Disk', 270, '2019-09-19 00:00:00.000'),
(16, 'Hard Disk', 210, '2019-08-12 00:00:00.000'),
(17, 'Hard Disk', 190, '2019-08-29 00:00:00.000'),
(18, 'Hard Disk', 480, '2019-07-25 00:00:00.000'),
(19, 'Hard Disk', 680, '2019-06-15 00:00:00.000'),
(20, 'Hard Disk', 120, '2019-06-28 00:00:00.000')

A simple CASE statement expression

A simple case statement evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.

Suppose, you want to display a short code of an item in result set such as If item name is ‘SCANNER’ then code will be ‘SCN’, If ‘PEN DRIVE’ then ‘PD’, and ‘HD’ for ‘HARD DISK’.

For such requirement you can use simple case statement, following simple case expression evaluates Item value against multiple given values and return the short code that is given in THEN, If condition and expression are matched.

 SELECT ID, ITEM, CASE ITEM 
  WHEN 'SCANNER' THEN 'SCN'
  WHEN 'PEN DRIVE' THEN 'PD'
  WHEN 'HARD DISK' THEN 'HD'
  ELSE 'NOT MATCHED' END CODE, ITEM_PURCHASED_DATE
FROM ITEM_DETAILS

As you can see Code column that contain the short code for an item.

The searched CASE expression:

It evaluates a condition using comparison operators, if condition is satisfied, It returns an expression corresponding THEN in the output.

Suppose, you want to set a quantity status based on the range of a quantity such as If quantity is between >=1 and <= 100 then status will be ‘Low Supply’, If quantity is between >100 and <=500 then status will be ‘Medium Supply’ , and status will be ‘High Supply’ for all quantity >500.




Following Search case expression returns the status based on the comparison of quantity and returns the status mentioned in THEN, if comparison expression is satisfied.

 SELECT ID, ITEM, ITEM_QTY,CASE 
   WHEN ITEM_QTY >=1 AND ITEM_QTY <=100 THEN 'LOW SUPPLY'
   WHEN ITEM_QTY >100 AND ITEM_QTY <=500 THEN 'MEDIUM SUPPLY'
   WHEN ITEM_QTY >500 THEN 'HIGH SUPPLY'
   ELSE 'NOT SATISIFED' END STATUS, ITEM_PURCHASED_DATE
FROM ITEM_DETAILS

As you can see, it evaluates an expression and returns the status for each quantity.

Implementing Nested Case Statement

You can also use implement nested cast statement as per your requirement.

Following statement uses case inside case to get the desired output.

SELECT ID, ITEM, ITEM_QTY, CASE -- Outer Case
WHEN ITEM ='Scanner' THEN
CASE WHEN ITEM_QTY >= 200 THEN 'High Qty' -- Nested Case
WHEN ITEM_QTY < 200 THEN 'Low Qty' 
END
WHEN ITEM ='Pen Drive' THEN
CASE WHEN ITEM_QTY >= 300 THEN 'High Qty' -- Nested Case
WHEN ITEM_QTY < 300 THEN 'Low Qty' 
END
WHEN ITEM ='Hard Disk' THEN
CASE WHEN ITEM_QTY >= 400 THEN 'High Qty' -- Nested Case
WHEN ITEM_QTY < 400 THEN 'Low Qty' 
END
END AS STATUS FROM dbo.ITEM_DETAILS

Case statement can only be nested to Level 10 only

You can only nest case statement up to 10 level only, If you try to nest case statement more than 10 you will get following error.

Following case expression is nested to 11 level, and you can see it returns an error.

DECLARE @number INT
SET @number = 11
SELECT 
 CASE WHEN @number >= 1 THEN -- Level 1
   CASE WHEN @number >= 2 then -- Level 2
     CASE WHEN @number >= 3 then -- Level 3
       CASE WHEN @number >= 4 then -- Level 4
         CASE WHEN @number >= 5 then -- Level 5
           CASE WHEN @number >= 6 then -- Level 6
             CASE WHEN @number >= 7 then -- Level 7
               CASE WHEN @number >= 8 then --Level 8
                  CASE WHEN @number >= 9 then--Level 9
                     CASE WHEN @number >= 10 then--Level 10
                        CASE WHEN @number >= 10 then--Level 11
                                  @number
End End End End End End End End End End
End AS Number

Lets try to this case expression up to 10 level only.

DECLARE @number INT
SET @number = 11
SELECT 
 CASE WHEN @number >= 1 THEN -- Level 1
   CASE WHEN @number >= 2 then -- Level 2
     CASE WHEN @number >= 3 then -- Level 3
       CASE WHEN @number >= 4 then -- Level 4
         CASE WHEN @number >= 5 then -- Level 5
           CASE WHEN @number >= 6 then -- Level 6
             CASE WHEN @number >= 7 then -- Level 7
               CASE WHEN @number >= 8 then --Level 8
                  CASE WHEN @number >= 9 then--Level 9
                     CASE WHEN @number >= 10 then--Level 10
                               @number
End End End End End End End End End 
End AS Number





CASE Statement with ORDER BY Clause

Sometime we have a requirement to sort the result set returned by select statement in a custom order , like to display any particular column value at top of result set, while remaining can be in standard sorting order.

Following statement displays the ITEM– ‘HARD DISK’ always at on the top of  in sorting result set , regardless whatever the sorting order is used in select statement .

 SELECT ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE
   FROM ITEM_DETAILS
 ORDER BY 
   CASE ITEM WHEN 'HARD DISK' THEN 1 ELSE 2 END

 

As you can see, all Item name that have value ‘HARD DISK’ is at on the TOP in sorting order, and remaining items follows the standard sorting order.

If you want read it in detail, read here – Custom sorting using case statement in Order By Clause

 SQL Server IF.. ELSE




 588 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.