Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Using select statement inside a CASE WHEN expression

    Hello,
    Need help in writing query for the below scenario:

    I have a table T1 which has columns say TM_ID and ALT_ID. I need to select a set of TM_IDs. If this ALT_ID has a value zero the I need to select all TM_IDs from another table T2 else the TM_ID from T1

    So here is the question.

    I need to select TM_IDs from table T1 for a particular ALT_ID. IF TM_ID is zero then I need to select all teams from another table T2. Else TM_ID from table T1. I tried using select inside a CASE like below :

    SELCT TM_ID from T1
    case TM_ID
    WHEN 0 THEN SELECT TM_ID from T2
    else
    TM_ID
    END
    from T1 where ALT_ID='222'

    But I end up is error -104

    Thanks for ur help

    Thanks,
    Thamin

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try those examples.
    (both were not tested)

    Example (a):
    Code:
    SELECT t2.*
     FROM  t2
     INNER JOIN
           t1
       ON  t1.tm_id  = t2.tm_id
       OR  t1.tm_id  = 0
     WHERE t1.alt_id = '222'
    Example (b):
    Code:
    SELECT t2.*
     FROM  t2
     WHERE EXISTS
           (SELECT 0
             FROM  t1
             WHERE t1.alt_id = '222'
               AND
               (   t1.tm_id  = t2.tm_id
                OR t1.tm_id  = 0
               )
           )
    "t1.tm_id = t2.tm_id OR t1.tm_id = 0" can be replaced by "t1.tm_id IN (t2.tm_id , 0)".
    Last edited by tonkuma; 07-11-11 at 09:14. Reason: Correct spelling(SELCT to SELECT).

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    Hello, Thanks for the reply. I tried your query and its wotking fine when there is non zero value for TM_ID in T1 and for zero value this is not fetching any rows... :-(

  4. #4
    Join Date
    Jul 2011
    Posts
    13
    hello,
    sorry i had a misplaced the two tables...your query is working fine...but just wanted to whether we can use SELECT inside CASE WHEN ...

    Thanks a lot!!!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    whether we can use SELECT inside CASE WHEN ...
    You can't do it.

    Although you can write SELECT statement(s) inside a CASE statement,
    it would be not as you expected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In Example (a) and Example (b),
    I assumed that all T1.tm_id were in T2.

    Example (c) would be exactly what you requested.
    I need to select TM_IDs from table T1 for a particular ALT_ID.
    IF TM_ID is zero then I need to select all teams from another table T2. Else TM_ID from table T1.
    Example (c):
    Code:
    SELECT COALESCE(t2.tm_id , t1.tm_id) AS tm_id
     FROM  t1
     LEFT OUTER JOIN
           t2
       ON  t1.tm_id = 0
     WHERE t1.alt_id = '222'
    ;

  7. #7
    Join Date
    Jul 2011
    Posts
    13
    Thanks a lot Ton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •