Q

CASE and PL/SQL, part 2

Why isn't CASE supported by PL/SQL? If I want to categorize data by range in a SELECT statement in PL/SQL, what options do I have? Here's what I can do in SQL:

 SELECT CASE WHEN a < 350 THEN 0 WHEN a BETWEEN 350 AND 730 THEN 1 WHEN a BETWEEN 731 AND 740 THEN 2 WHEN a BETWEEN 741 AND 760 THEN 3 WHEN a > 760 THEN 0 WHEN a IS NULL THEN -1 ELSE 0 END AS a FROM b;


Continued from page 1.

If you can't use CASE in PL/SQL, you can:

 

Use IF ... ELSIF ...

If you have to use Oracle 8.1 (or earlier), PL/SQL's IF ... ELSIF ... provides the functionality of a searched CASE expression, using very similar syntax. To save space, the remaining examples will not show all the comments.

 CREATE OR REPLACE FUNCTION foo ( in_val IN NUMBER -- Number to be categorized ) RETURN PLS_INTEGER IS return_val PLS_INTEGER; BEGIN IF in_val < 350 THEN return_val := 0; ELSIF in_val <= 730 THEN return_val := 1; ELSIF in_val <= 740 THEN return_val := 2; ELSIF in_val <= 760 THEN return_val := 3; ELSIF in_val > 760 THEN return_val := 0; ELSIF in_val IS NULL THEN return_val := -1; ELSE return_val := 0; END IF; RETURN return_val; END foo; /

If you're unfamiliar with ELSIF, it's just a cleaner way of writing nested IF statements. The two examples below produce the same results:

 -- Using nested IF IF in_date < (SYSDATE - .5) THEN y_val := 1; y_text := 'Past' ELSE IF in_date < (SYSDATE + .5) THEN y_val := 2; y_text := 'Present'; ELSE y_val := 3; y_text := 'Future'; END IF; END IF;
 -- Using ELSIF IF in_date < (SYSDATE - .5) THEN y_val := 1; y_text := 'Past' ELSIF in_date < (SYSDATE + .5) THEN y_val := 2; y_text := 'Present'; ELSE y_val := 3; y_text := 'Future'; END IF;

 

Use a SQL View

You can't use CASE in PL/SQL 8.1, but you can reference a view that uses it. You might be able to work around your problem like this:

 CREATE OR REPLACE VIEW b_plus AS SELECT a, CASE WHEN a < 350 THEN 0 WHEN a BETWEEN 350 AND 730 THEN 1 WHEN a BETWEEN 731 AND 740 THEN 2 WHEN a BETWEEN 741 AND 760 THEN 3 WHEN a > 760 THEN 0 WHEN a IS NULL THEN -1 ELSE 0 END AS a_minus FROM b;

Your PL/SQL code can use this view instead of the base table. I find a certain perverse pleasure in this solution. Often I've needed a view that included some value that was difficult to calculate in SQL, so I wrote a PL/SQL function to use in the view. Here we're doing just the opposite.

By the way, it would be infintesimally faster not to test for a < 350 and a > 760, since they yield the default value anyway. I like the way you wrote the CASE statement because it's easy to understand and maintain, but be aware that the following produces the same results:

 CASE -- WHEN a < 350 THEN 0 -- Handled by ELSE WHEN a BETWEEN 350 AND 730 THEN 1 WHEN a BETWEEN 731 AND 740 THEN 2 WHEN a BETWEEN 741 AND 760 THEN 3 -- WHEN a > 760 THEN 0 -- Handled by ELSE WHEN a IS NULL THEN -1 ELSE 0 END

 

Use DECODE

DECODE can do anything that CASE can do. I don't recommend DECODE for this problem, but you keep it in mind for other instances of this problem, especially if they involve discrete values or regular intervals between ranges.

This was first published in November 2003
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close