This function works...
CREATE OR REPLACE FUNCTION myfunc (a_code VARCHAR2) RETURN NUMBER DETERMINISTIC IS v_retval NUMBER; BEGIN CASE Upper(a_code) WHEN 'FIRST' THEN v_retval := 1; WHEN 'SECOND' THEN v_retval := 2; WHEN 'THIRD' THEN v_retval := 3; WHEN 'FOURTH' THEN v_retval := 4; ELSE v_retval := -1; END CASE; RETURN v_retval ; END myfunc;
However, I've seen conflicting descriptions about how the case statement works. It seems to behave only like a control structure. I was expecting to to be able to use it like an expression:
v_retval := case upper(a_code) when 'FIRST' then 1 when 'SECOND' then 2 when 'THIRD' then 3 when 'FOURTH' then 4 else -1 end case;
But I can't seem to get case to behave like an expression, although some of the documentation suggests that it should.
What am I doing wrong?
Answer by KillerDBA ·
OK, I think I figured it out... the "end case" at the end doesn't work for a case expression, it's only for a case statement. This:
CREATE OR REPLACE FUNCTION myfunc (a_code VARCHAR2) RETURN NUMBER DETERMINISTIC IS v_retval NUMBER; BEGIN v_retval := CASE Upper(a_code) WHEN 'FIRST' THEN 1 WHEN 'SECOND' THEN 2 WHEN 'THIRD' THEN 3 WHEN 'FOURTH' THEN 4 ELSE -1 END; RETURN v_retval; END myfunc;
works fine. Of course, further illumination is always welcome.