SQL CASE statement with Multiple THEN's

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?

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

Unless you return an object.

Code:

---

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.

---

> Unless you return an object.

Yes. Or a delimited string...

Code:

---

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 ;-)

That or a collection type.

Code:

---

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 ;)

Yes, how frightfully remiss of me :rolleyes:

Then again you could use package global variables...

Code:

---

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.

Just remember,

Nobody expects the Spanish Inquisition.

John

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

Code:

---

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

---

Quote:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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

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.

Hi.

I see the same issue with the context approach:

Code:

---

CREATE OR REPLACE CONTEXT app_ctx USING set_ctx;

CREATE OR REPLACE PACKAGE set_ctx AS
FUNCTION set_attr (p_attr1  IN  VARCHAR2,
                  p_attr2  IN  VARCHAR2)
  RETURN VARCHAR2;
END set_ctx;
/


CREATE OR REPLACE PACKAGE BODY set_ctx IS

FUNCTION set_attr (p_attr1  IN  VARCHAR2,
                    p_attr2  IN  VARCHAR2)
  RETURN VARCHAR2 IS
BEGIN
  DBMS_SESSION.set_context('app_ctx', 'attr1', p_attr1);
  DBMS_SESSION.set_context('app_ctx', 'attr2', p_attr2);
  RETURN 'COMPLETE';
END;

END set_ctx;
/
SHOW ERRORS


SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
,      SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
FROM  (SELECT CASE
                WHEN 1 = 1 THEN
                  set_ctx.set_attr ('definitely', 'not')
                END
      FROM  dual
      ORDER BY 1);


GET_1
-----------------------------------------------
GET_2
-----------------------------------------------




1 row selected.


SELECT CASE
                WHEN 1 = 1 THEN
                  set_ctx.set_attr ('definitely', 'not')
                END
      FROM  dual
      ORDER BY 1;
     
CASEWHEN1=1THENSET_CTX.SET_ATT
--------------------------------
COMPLETE

1 row selected.

SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
,      SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
FROM dual;


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


1 row selected.

---

Am I going mad?

Cheers

Tim...

You're not going mad ( well almost, but not quite ;) ). It's something to do with FAST DUAL, as mentioned on my previous post. If we use a pipelined function as a single row source instead, it works:-

Code:

---

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  TABLE( one_row )
  8          ORDER BY 1);

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

---

Wonder what optimisation FAST DUAL has that makes it behave like this?

Regards
Adrian

Quote:

---

Originally Posted by 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.

---

Sorry, didn't see this before posting the thing about the context suffering the same fate.

Cheers

Tim...

Actually, FAST DUAL might be a red herring. Forcing "normal" DUAL doesn't see to make a difference either.

Code:

---

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          ,      dummy
  8          FROM  dual
  9          ORDER BY 1);

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


SQL>
SQL>
SQL> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  79rukd61zu5sr, child number 0
-------------------------------------
SELECT package_name.get_a,        package_name.get_b FROM  ( SELECT
(CASE                  WHEN 1 = 1 THEN
package_name.set_a_b (1, 2)                  END)        ,      dummy
        FROM  dual          ORDER BY 1)

Plan hash value: 2367845099

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |      |      |    3 (100)|          |
|  1 |  VIEW              |      |    1 |      |    3  (34)| 00:00:01 |
|  2 |  TABLE ACCESS FULL| DUAL |    1 |    2 |    2  (0)| 00:00:01 |
---------------------------------------------------------------------------

---

Regards
Adrian

Right, this is my last post on this one. Including a column in the outer projection seems to help. Without it, there is no projection at all. I still think this is a bug as I can't see what difference this could make as the plans are the same.

Code:

---

SQL> exec dbms_session.reset_package;

PL/SQL procedure successfully completed.

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

    GET_A      GET_B SOME_COL
---------- ---------- ---------------
        1          2 COMPLETE

---

I like the object type method best ;o)

Regards
Adrian