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?