Using select statement inside a CASE WHEN expression

thamin · #1 (**permalink**) 07-11-11, 07:02

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

tonkuma · #2 (**permalink**) 07-11-11, 08:07

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

thamin · #3 (**permalink**) 07-11-11, 09:19

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

thamin · #4 (**permalink**) 07-11-11, 09:28

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

tonkuma · #5 (**permalink**) 07-11-11, 09:42

Quote:

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.

tonkuma · #6 (**permalink**) 07-11-11, 10:17

In Example (a) and Example (b),
I assumed that all T1.tm_id were in T2.

Example (c) would be exactly what you requested.

Quote:

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

thamin · #7 (**permalink**) 07-12-11, 10:10

Thanks a lot Ton

Internet Services

Web Development

Digital Marketing

Consumer Tech