Case Statement - Statement vs Expression in the Where Clauses?

Question

question

KillerDBA asked Nov 19, '09

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?

plsql developer

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-19T13:28:17Z

KillerDBA answered Nov 19, '09

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.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA · Nov 20, 2009 at 01:19 PM

Curiously, I have found examples in existing code that appear to work where "end case" at the end does appear to be accepted for a Case expression in a SQL statement. I'm still somewhat mystified.

0 ·

KillerDBA · Dec 07, 2009 at 11:35 AM

I've read through a number of documents, forums, etc, and nothing specifically calls out the difference, although all do indicate "end case" for statement and "end" for an expression.

0 ·

question