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
|
