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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close