SQL CASE statement with Multiple THEN's

[mozart]

I am wondering whether its possible to extract two seperate columns of data after a 'THEN' statement when using CASE

For example

Select country,
CASE WHEN X>1 Then (code which allows 'A' in one column and 'B' in a second column)...........
END
FROM TABLE_A

Can't find anything in my 9i query books. Is there a way?

[TimHall]

Hi.

The answer is no. The case expression you are talking about is used to derive a value of a single column. It's not like the PL/SQL cas statement that can have whole blocks of code in it.

This might help you understand the difference between a case expression and a case statement.

http://www.oracle-base.com/articles/9i/Case9i.php

Cheers

Tim...

[WilliamR]

Unless you return an object.

SQL> CREATE TYPE INTEGER_PAIR_OT AS OBJECT
  2  ( i1 INTEGER
  3  , i2 INTEGER )
  4  /

Type created.

SQL> SELECT v.expr.i1
  2       , v.expr.i2
  3  FROM   ( SELECT CASE
  4                      WHEN 'x' = 'y' THEN INTEGER_PAIR_OT(0,1)
  5                      ELSE INTEGER_PAIR_OT(2,3)
  6                  END AS expr
  7           FROM   dual ) v;

   EXPR.I1    EXPR.I2
---------- ----------
         2          3

1 row selected.

[padders]

> Unless you return an object.

Yes. Or a delimited string...

SQL> SELECT SUBSTR (expr, 1, 
  2                 INSTR (expr, '|') - 1) a,
  3               SUBSTR (expr, 
  4                  INSTR (expr, '|') + 1) b
  5  FROM  (SELECT CASE
  6                  WHEN 'x' = 'y' THEN 
  7                    '0|1'
  8                  ELSE 
  9                    '2|3'
 10                  END AS expr
 11         FROM   dual) v;

A   B
--- ---
2   3

SQL>

...but otherwise no, definitely not ;-)

[WilliamR]

That or a collection type.

SQL> CREATE OR REPLACE TYPE integer_tt AS TABLE OF INTEGER;
  2  /

Type created.

SQL> SELECT t.COLUMN_VALUE
  2  FROM   TABLE
  3         ( CASE
  4               WHEN 'x' = 'y' THEN integer_tt(1,2)
  5               ELSE integer_tt(3,4)
  6           END ) t;

COLUMN_VALUE
------------
           3
           4

2 rows selected.

But those are the ONLY three ways

[padders]

Yes, how frightfully remiss of me

Then again you could use package global variables...

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          2

SQL>

...but there definitely aren't any other ways.

[John Spencer]

Just remember,

Nobody expects the Spanish Inquisition.

John

[billiauk]

Leaping on that bandwagon, you most definitely cannot use application contexts:-

SQL> SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
  2  ,      SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
  3  FROM  (SELECT CASE
  4                  WHEN 1 = 1 THEN
  5                    set_ctx.set_attr ('definitely', 'not')
  6                  END
  7         FROM   dual
  8         ORDER BY 1);

GET_1      GET_2
---------- ----------
definitely not

[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...

[billiauk]

Interesting this. Same is true on Windows. At first I thought it could be merging, but I see that Padders included an ORDER BY, presumably for this very reason ( i.e. force no merge ).

The only difference in 10g with this that I can see is the FAST DUAL optimisation. I can't see anything else in trace. It could be a bug of course. It doesn't appear to be anything to do with the PLSQL_OPTIMIZE_LEVEL either - setting this to 0 made no difference.