SQL CASE expression

How often have you been in a situation where you wanted to do an IF/ELSE in a SQL query. How often have you then written “SELECT … DECODE (expression, value, return, value, return, default return) FROM….” or even worse and selected the values and did then an IF/ELSE in PL/SQL?

The good thing in Oracle is that you can do a really IF/ELSE in the SQL query using the CASE expression. The documentation says following:

CASE expressions let you use IFTHENELSE logic in SQL statements without having to invoke procedures. The syntax is:

CASE { simple_case_expression
     | searched_case_expression
     [ else_clause ]

Ok here first a short example of the “simple_case_expression”:

2            CASE salery
3                 WHEN 1000 THEN ‘Low’
4                 WHEN 2000 THEN ‘Medium’
5                 WHEN 3000 THEN ‘Medium’
6                 ELSE ‘High’
7           END
8           FROM employee;

NAME                      CASESA
————————- ——
John                      Low
Andreas               Medium
Harry                    Medium
Mike                      High

So here you can see a simple IF/ELSIF/ELSE based on a column value. But what if you have some more complex logic like comparing two values with and AND. Well also that works in the “searched_case_expression”:

SQL> select name,
2             CASE
3                 WHEN salery = 1000 THEN ‘Low’
4                 WHEN salery = 2000 OR salery = 3000 THEN ‘Medium’
5                 ELSE ‘High’
6             END
7             FROM employee;

NAME                      CASEWH
————————- ——
John                        Low
Andreas                 Medium
Harry                      Medium
Mike                         High

Here you can see that I’m using  an OR clause like in the ELSIF. That also works with bind variables of course!

So this should help you in future when you have a select and you think of DECODE or PL/SQL IF/ELSE!

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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