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