All Forums UDA
ylai20 4 posts Joined 09/09
05 Nov 2009
How to add in Select Statement inside Case?

I have 2 table with below dataManager_ID | Manager_Name | COMPANYCARE | ANDY | COMP ACARE | JOHN | COMP BCARE | CHRIS | COMP CA001 | ANDY | COMP DA002 | MABEL | COMP EA003 | LANDY |COMP FMANAGER_ID | MANAGER_NAMEA001 | ANDYA002 | MABELA003 | LANDYA004 | JOHNA005 | CHRISI 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_ID2CARE | ANDY | COMP A | A001CARE | JOHN | COMP B | A004CARE | CHRIS | COMP C | A005A001 | ANDY | COMP D | A001A002 | MABEL | COMP E | A002A003 | LANDY |COMP F | A003My 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) elseT1.Manager_ID END as Manager_ID2)From DB.Table1 T1But this code seems got problem..anyone can help?Thanks!

pawan0608 101 posts Joined 12/07
05 Nov 2009

You can try thisSELECT T1.Manager_ID, T1.Manager_Name, T1.COMPANY, T2.Manager_ID AS Manager_ID2FROM DB.Table1 T1, DB.Table2 T2 WHERE T1.Manager_Name = T2.Manager_NameAND T1.Manager_ID like '%CARE%'UNION ALLSELECT T1.Manager_ID, T1.Manager_Name, T1.COMPANY, T1.Manager_ID AS Manager_ID2FROM DB.Table1 T1WHERE T1.Manager_ID NOT like '%CARE%'

Shashi Kant 1 post Joined 11/09
05 Nov 2009

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_IdEND as Manager_ID2FromTable1 T1left joinTable2 T2on T1.Manager_Name = T2.Manager_Name

abidha 1 post Joined 06/11
01 Jun 2011

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

You must sign in to leave a comment.