Ask the Expert

CASE and PL/SQL, part 1

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: