EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials DB2 Tutorial DB2 case statement
 

DB2 case statement

Updated March 6, 2023

DB2 case statement

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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];

DB2 1

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 –

Number of records

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];

DB2

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 –

  1. DB2 LISTAGG
  2. What is DB2?
  3. DB2 Interview Questions
  4. Data Warehouse Software

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW