Why isn't CASE supported by PL/SQL? If I want to categorize data by range in a SELECT statement in PL/SQL, what...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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 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
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.