Updated March 6, 2023
Introduction to DB2 case statement
DB2 case statement is available in IBM which helps us to make the use of conditional behavior. The case statement works similar to the if-else statement in the coding languages. There are two types of case statements supported by DB2 which are simple case statement and the searched case statement. Both the case statements work in a similar manner and help to implement multiple expression evaluation and comparison based on which the result set can be modified. We can make use of case statement anywhere in our query as it is an expression. Hence, case statement can be used inside the SELECT statement, WHERE clause, GROUP BY clause, and even inside the HAVING clause of the query statement.
In this article, we will study the general syntax of the simple case statement and searched case statement and we will also study the implementation of this statements along with the examples in DB2 database management system.
Simple Case statement:
Simple case statement comes with multiple optional WHEN clause which helps us to mention multiple possible cases of the expression that is passed to it according to which the Let us firstly see the syntax of the simple case statement which is as shown below-
CASE exp
WHEN exp 1 THEN selected output 1
WHEN exp 2 THEN selected output 2
...
WHEN exp n THEN selected output n
[ ELSE output for else]
END
In the above expression, exp 1, exp 2 are the expressions that will evaluate to a particular value. The evaluated value of the expression is matched with exp and then accordingly the selected output for that value is given as the output result. In case none of the expression gets matched then if specified the output for else mentioned after ELSE clause is considered as the final output of the select statement.
If all the expressions fail to match and we have not specified the ELSE clause as well then, a default NULL value is returned from the case statement. Most of the times, we make use of ISNULL or COALESCE function to handle the NULL values returned by the case statement.
Let us consider one example of the simple case statement. We have one tale named Sales_Customers which contains the values as shown in the below output of the following query statement:
SELECT * FROM [Sales_Customers];
Now, suppose that we have a condition that if the mobile number begins from 914568524 having all the nine characters as mentioned then we have to retrieve the mobile number value else we have to retrieve an encoded mobile number with ********** value as the output. In this case, we can make the use of a simple case statement by specifying 914568524 as the expression in string format, and the WHEN clause will contain the substring function to get the first 9 characters of the mobile number field.
SELECT CASE "914568525"
WHEN SUBSTRING(mobile_number, 1, 9)
THEN mobile_number
ELSE "**********" END
as "Mobile Number"
FROM [Sales_Customers];
The execution of above query statement gives out the following output containing all the mobile numbers that have “914568525” string in the beginning and “**********” in case if the expression doesn’t matches the substring of mobile number field value as shown below –
Searched case statement:
In case of searched statement, we do not go for matching the expression values with the other expressions mentioned inside the WHEN clause of the CASE statement instead the expressions that are mentioned in the WHEN clause needs to evaluate to a Boolean value. If any of the expression evaluates to true the search or execution of the case statement stops there itself and the output mentioned after that corresponding when clause is considered for the final output of the case statement.
If none of the expression evaluates to true then the case statement returns NULL as the output. If we don’t want to show NULL values as the output then we have to make use of ISNULL, IFNULL, or COALESCE functions to handle NULL values and display any of the alternative value in that place.
The syntax of the searched case statement is as shown below-
CASE
WHEN exp 1 THEN selected output 1
WHEN exp 2 THEN selected output 2
...
WHEN exp n THEN selected output n
[ ELSE output for else]
END
Just the difference between simple and searched statement is the mention of the expression after CASE clause for matching. Let us now consider one example of the searched CASE statement. Consider the same previous case where we were trying to display the mobile numbers only when the begin with a particular string value with which we are evaluating using LIKE statement which will return a Boolean value as soon as it gets true it goes for retrieving the column value of mobile_number else it fetches “**********” which is used to represent encoded string if expression evaluates to false. Our query statement will now look as shown below –
SELECT case
when mobile_number like "914568524%"
then mobile_number ELSE "**********"
END
as "Mobile Number"
FROM [Sales_Customers];
The output of the above query statement is as shown below which is same as that of while using the simple case statement.
Conclusion
We can make the use of case statement to induce if-else or conditional behavior in our query statements. There are two type of case statements which are simple case statement and searched case statement. In a simple case statement, we have to find out the matching expression value for all the specified when clauses and then for the matching WHEN clause the THEN clause is given as output else it goes for giving out the expression value mentioned in ELSE if specified else it gives NULL value. In case of searched case statement, we have to specify the expressions after WHEN clause such that they will evaluate to a Boolean value. The expression whose value gives true is considered for the final output else it goes for giving expression after ELSE clause if specified else NULL value.
Recommended Articles
This is a guide to DB2 case statement. Here we discuss the Introduction, syntax, examples with code implementation. You may also have a look at the following articles to learn more –
