Case Statement - Statement vs Expression in the Where Clauses?
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?
Best Answer
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.
