EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more