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
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.