using sql case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using sql case

Author

Topic

yasinirshad
Starting Member

18 Posts

Posted - 2008-06-08 : 04:51:05

Hi,
am using case in sql, my query is below..

select eC_ID,
case eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master

My question is how do I use a case inside a case statement in sql. i.e., when case is '2' .,for below line.,

when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')

I need to check first EMPLOYEE_DIVISION..If its 'FIN',then i need to execute below query,

(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP')

else i need to execute,

when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')

So i have to use 2 tables..one is eC_Master and another Users. Can i use like below.

select A.eC_ID,
case A.eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then 
case B.EMPLOYEE_DIVISION
WHEN 'FIN' THEN
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP') ELSE
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS') END
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master A inner join Users B on A.eC_Requestor_ID=B.EMPLOYEE_ID

THANKS TO HELP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-08 : 05:20:15

You cant return result set like this through case statement. It will only return a single value for each value of eC_FormID. Can you please specify what your exact requirement is?