SQL CASE statement with Multiple THEN's

[TimHall]

Nobody expects the Spanish Inquisition.

Hey, when you're wrong, you're just plain wrong! No inquisition here.

OK. Guilty. I was not thinking outside the box here.

But in my defense, in all examples the case expression itself is actually returning a single column value. You are then processing this value, or in some cases completely ignoring this, to get the result. The case statement itself is not allowing you to pass multiple columns back.

In a sense, the answer is correct in that only a single column can be returned. What you do with that column is a different matter entirely. Of course, if that column was a composite of several pieces of information then the answer is, "Yes it can be done provided you process the information further."

The other approaches seem very normal, but I'm interested in the package global approach. My instant reaction was that this couldn't work as you can't call a procedure from within an SQL statement. I then guessed that this was infact a function call, probably returning a dummy value as it's not being used. I figured you must be doing something like:

CREATE OR REPLACE PACKAGE package_name AS
  FUNCTION set_a_b(p_a  IN  NUMBER,
                   p_b  IN  NUMBER) RETURN VARCHAR2;
  FUNCTION get_a RETURN NUMBER;
  FUNCTION get_b RETURN NUMBER;
END package_name;
/

CREATE OR REPLACE PACKAGE BODY package_name AS
  g_a  NUMBER;
  g_b  NUMBER;
  
  FUNCTION set_a_b(p_a  IN  NUMBER,
                   p_b  IN  NUMBER) RETURN VARCHAR2 IS
  BEGIN
    g_a := p_a;
    g_b := p_b;
    RETURN 'COMPLETE';
  END;
  
  FUNCTION get_a RETURN NUMBER IS
  BEGIN
    RETURN g_a;
  END;
  
  FUNCTION get_b RETURN NUMBER IS
  BEGIN
    RETURN g_b;
  END;
END package_name;
/

The interesting thing about this is I can't get it to work

If I run the query I get:

SQL> SELECT package_name.get_a, 
  2         package_name.get_b
  3  FROM  (SELECT (CASE 
  4                  WHEN 1 = 1 THEN
  5                    package_name.set_a_b (1, 2)
  6                  END)
  7         FROM   dual
  8         ORDER BY 1);

     GET_A      GET_B
---------- ----------


1 row selected.

SQL>

But if I run the query from the from clause followed by the outer query I get:

SQL> SELECT CASE 
  2                  WHEN 1 = 1 THEN
  3                    package_name.set_a_b (1, 2)
  4                  END
  5         FROM   dual
  6         ORDER BY 1;

CASEWHEN1=1THENPACKAGE_NAME.SET_A_B(1,2)END
-----------------------------------------------------------------------
COMPLETE

1 row selected.

SQL> SELECT package_name.get_a, package_name.get_b from dual;

     GET_A      GET_B
---------- ----------
         1          2

1 row selected.

SQL>

I've run this on 10g under Windows and Tru64 with the same result.

Have I missed something really obvious here or does this solution not really work?

Cheers

Tim...