Q

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 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 last published in November 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close