SQL CASE expressions in DB2 for i SQL

SQL CASE expressions in DB2 for i SQL

CASE statement

The SQL CASE statement is a conditional statement. The CASE statement works like a simple IF-THEN-ELSE statement. It test multiple condition and return a value when the first condition is satisfied.

CASE syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

If we see the syntax of SQL CASE statement above we observed that it starts with keyword CASE and followed by multiple conditional statement. Each conditional statement consists of at least one pair of WHEN and THEN statements where WHEN specifies the conditional statement to be tested and THEN specifies the action to be taken if the respective WHEN condition gets satisfied.

If none of the WHEN conditions are TRUE then it returns value mentioned in ELSE statement.

If none of the WHEN conditions are TRUE and ELSE part is also not mentioned then it returns NULL value.

Example #1: Create another column based on existing column using CASE statement.

Refer STUDENT table data here.

Data in STUDENT table:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Using CASE statement

SELECT ROLLNO, NAME, SubjectId,                      
CASE                                      
    WHEN SubjectId = 201 THEN 'Computer'  
    WHEN SubjectId = 202 THEN 'Science'   
    ELSE 'Subject Name not assigned'      
END AS SubjectName                        
FROM Student                              

This can also be written as:

SELECT student.*,                        
CASE                                     
    WHEN SubjectId = 201 THEN 'Computer' 
    WHEN SubjectId = 202 THEN 'Science'  
    ELSE 'Subject Name not assigned'     
END AS SubjectName                       
FROM Student                             

Used case statement and created a new column based on existing column and assigned a name after END Case statement using AS clause.

ROLLNO   NAME                      SUBJECTID   SUBJECTNAME              
     1   Ankur                           201   Computer                 
     2   Rahul                           202   Science                  
     3   Raman                           203   Subject Name not assigned
     4   Vimal                           204   Subject Name not assigned
     5   Samar                           290   Subject Name not assigned

Example #2: CASE statement with ORDER BY clause.

Refer STUDENT table data here.

Data in STUDENT table:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Using CASE statement with order by to sort the result in descending order based on NAME or ROLLNO depending upon the value of the NAME column. If the Name starts with 'R' then the result is sorted by the name column otherwise the result is sorted by the SubjectName column.

SELECT student.*,                       
CASE                                    
    WHEN SubjectId = 201 THEN 'Computer'
    WHEN SubjectId = 202 THEN 'Science' 
    ELSE 'Subject Name not assigned'    
END AS SubjectName                      
 FROM student                           
ORDER BY                                
(                                       
CASE                                    
WHEN NAME LIKE 'R%' THEN NAME           
ELSE SubjectName                        
END                                     
) desc                                  

ROLLNO   NAME                      SUBJECTID   SUBJECTNAME              
     4   Vimal                           204   Subject Name not assigned
     5   Samar                           290   Subject Name not assigned
     3   Raman                           203   Subject Name not assigned
     2   Rahul                           202   Science                  
     1   Ankur                           201   Computer                 

1st and 2nd rows are sorted in descending order based on derived column SubjectName.

3rd and 4th rows are sorted descending order based on table column Name.

5th row is again sorted in descending order based on derived column SubjectName.

If sorted in ascending order then the result would look like:

SELECT student.*,                       
CASE                                    
    WHEN SubjectId = 201 THEN 'Computer'
    WHEN SubjectId = 202 THEN 'Science' 
    ELSE 'Subject Name not assigned'    
END AS SubjectName                      
 FROM student                           
ORDER BY                                
(                                       
CASE                                    
WHEN NAME LIKE 'R%' THEN NAME           
ELSE SubjectName                        
END                                     
)                               

ROLLNO   NAME                      SUBJECTID   SUBJECTNAME               
     1   Ankur                           201   Computer                  
     2   Rahul                           202   Science                   
     3   Raman                           203   Subject Name not assigned 
     4   Vimal                           204   Subject Name not assigned 
     5   Samar                           290   Subject Name not assigned 

1st row is sorted based on derived column SubjectName.

2nd and 3rd rows are sorted based on table column Name.

4th and 5th rows are again sorted based on derived column SubjectName.

Example #3: Simple WHEN clause in CASE statement

let's right the Example#1 query using Simple WHEN clause in CASE statement.

Here, the value of the expression prior to the 1st when keyword is tested for equality with the value of each expression that follows the WHEN keyword. It also specifies the result for when that condition is true.

Refer STUDENT table data here.

SELECT student.*,                   
CASE SubjectId                      
    WHEN 201 THEN 'Computer'        
    WHEN 202 THEN 'Science'         
    ELSE 'Subject Name not assigned'
END AS SubjectName                  
FROM Student                        

Output:

ROLLNO   NAME                      SUBJECTID   SUBJECTNAME              
     1   Ankur                           201   Computer                 
     2   Rahul                           202   Science                  
     3   Raman                           203   Subject Name not assigned
     4   Vimal                           204   Subject Name not assigned
     5   Samar                           290   Subject Name not assigned

Example #4: Search WHEN clause in CASE statement

Example #1 is the perfect example for this.

Here, the pairwise comparison is performed and condition to be tested is specified with each WHEN clause along with action to be taken.