DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: SQL CASE statement with Multiple THEN's

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    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?

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    > 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 ;-)

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    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.

  7. #7
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Just remember,

    Nobody expects the Spanish Inquisition.

    John

  8. #8
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    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

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  10. #10
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    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
  •  


Click Here to Expand Forum to Full Width