How to add in Select Statement inside Case?

UDA
N/A

How to add in Select Statement inside Case?

I have 2 table with below data

Manager_ID | Manager_Name | COMPANY
CARE | ANDY | COMP A
CARE | JOHN | COMP B
CARE | CHRIS | COMP C
A001 | ANDY | COMP D
A002 | MABEL | COMP E
A003 | LANDY |COMP F

MANAGER_ID | MANAGER_NAME
A001 | ANDY
A002 | MABEL
A003 | LANDY
A004 | JOHN
A005 | CHRIS

I want the table A to have one more column (Manager ID_2). If the manager id = care, then this new column will get the Manager ID from Table 2, using Table1.Manager_Name = Table2.Manager_Name. When the manager id <> care, then the new column will remain as Table1.Manager_ID.

Manager_ID | Manager_Name | COMPANY | Manager_ID2
CARE | ANDY | COMP A | A001
CARE | JOHN | COMP B | A004
CARE | CHRIS | COMP C | A005
A001 | ANDY | COMP D | A001
A002 | MABEL | COMP E | A002
A003 | LANDY |COMP F | A003

My statement is
Select Manager_ID, Manager_Name, COMPANY,
case when T1.Manager_ID like '%CARE%' then
(
(SELECT T2.Manager_ID from DB.Table2 T2 where T1.Manager_Name=T2.Manager_Name)
else
T1.Manager_ID
END as Manager_ID2
)
From DB.Table1 T1

But this code seems got problem..anyone can help?
Thanks!
3 REPLIES

Re: How to add in Select Statement inside Case?

You can try this

SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
T2.Manager_ID AS Manager_ID2
FROM DB.Table1 T1,
DB.Table2 T2
WHERE T1.Manager_Name = T2.Manager_Name
AND T1.Manager_ID like '%CARE%'

UNION ALL

SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
T1.Manager_ID AS Manager_ID2
FROM DB.Table1 T1
WHERE T1.Manager_ID NOT like '%CARE%'

Re: How to add in Select Statement inside Case?

SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
Case when T1.Manager_id = 'CARE' and T1.Manger_Name = T2.Manager_Name
then T2.Manager_ID
ELSE T1.Manger_Id
END as Manager_ID2
From
Table1 T1
left join
Table2 T2
on T1.Manager_Name = T2.Manager_Name
N/A

Re: How to add in Select Statement inside Case?

Is there any other way to use the Select query inside the Case statement???? & can we make use of Select statment after 'THEN'

SELECT * FROM t1
WHERE
C1 = (
CASE WHEN ( SELECT Count(*) from t1 WHERE C2 = 12345 AND C3 ='XYZ' ) >0 THEN
SELECT C1 from t1 WHERE C2 = 12345 and c3 ='XYZ'
ELSE
SELECT C1 from t1 WHERE C2 = 12345
END)

When ran this is retrurning 3706 : Expected something between ')' and '='
**************
I tried modifying the inner Select query as,
SELECT * FROM t1
WHERE
C1 =
( CASE WHEN ( SELECT Count(*) from t1 WHERE C2 = 12345 AND C3 ='XYZ' ) >0 THEN
CASE WHEN C2 = 12345 and c3 ='XYZ' THEN
C1
END
ELSE
CASE WHEN C2 = 12345 THEN
C1
END
END)

Is there any other way to optimise this query.
This alos returns the same error 3706.
Thanks,
Abidha