-
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
Last edited by WilliamR; 07-04-2005 at 11:10 AM.
-
Yes, how frightfully remiss of me
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
-
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...
Last edited by TimHall; 07-05-2005 at 03:41 AM.
-
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.
Last edited by billiauk; 07-05-2005 at 04:10 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|