Venzi's Tech-Blog

16 January 2010

SQL CASE expression

Filed under: Oracle — Venzi @ 00:31

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

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

SQL> SELECT name,
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!

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: