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?