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;
If
Requires Free Membership to View
you can't use CASE in PL/SQL, you can:
Upgrade to Oracle 9
Case was introduced into SQL in Oracle version 8.1, but it didn't make it into PL/SQL until version 9.0. That's not unusual: Other new features were not available in PL/SQL when they were introduced. I don't know why there's a delay, and it certainly can be inconvenient, but I'm happy to get new features in SQL as soon as I can without waiting for Oracle to implement them in PL/SQL as well.
This function works in version 9.0 (and later):
CREATE OR REPLACE FUNCTION foo ( in_val IN NUMBER -- Number to be categorized ) RETURN PLS_INTEGER -- ************* -- ** f o o ** -- ************* -- foo returns an integer between -1 and 3 inclusive -- that categorizes the value of in_val according to -- yadda yadda yadda. -- If in_val is between 350 and 760 (inclusive), foo returns -- a number between 1 and 3. (Higher inputs always result -- in equal or higher outputs.) -- foo returns 0 if in_val is outside that range, and -- -1 if in_val IS NULL. IS BEGIN RETURN CASE WHEN in_val < 350 THEN 0 WHEN in_val <= 730 THEN 1 WHEN in_val <= 740 THEN 2 WHEN in_val <= 760 THEN 3 WHEN in_val > 760 THEN 0 WHEN in_val IS NULL THEN -1 ELSE 0 END; END foo; /
In your original query, you could use BETWEEN operators because you knew the column b.a was an integer. I changed the comparisons to use <= because I wanted a general function, a function that I could use today on b.a, but also a function that wouldn't require modification a year from now if I decided to use it on some other column that had values like 730.5.
Continued on page
2
This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation