CASE in subqueries

Transact-SQL
1

I have a sub query in which I am getting error:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I know the problem is in select that I can't have the column but how can I include the total_gross within this select?
Thanks.

I need to include the total_gorss within this erncode.

Blockquote

,(select total_gross, erncode,
case
when erncode ='5' then 'Sal'
when erncode='55' then 'OclLump'
when erncode='35' then 'Promo'
when erncode='38' then 'Lump'
when erncode='39' then 'Reclass'
when erncode='40' then ' LatT'
end
from EMployee )

Blockquote

2

you have 2 total_gross, erncode and it wants you to choose one of them

3

select total_gross,
case erncode
when erncode ='5' then 'Sal'
when erncode='55' then 'OclLump'
when erncode='35' then 'Promo'
when erncode='38' then 'Lump'
when erncode='39' then 'Reclass'
when erncode='40' then ' LatT'
end
from EMployee

4

Thanks I have tried this but it doesn't work.
Pasi

5

please help us understand ..

what you mean by

I need to include the total_gorss within this erncode.
6

the total_gross, erncode both in the table employee, so I need to include both in CASE statement. not sure if doable or not?
Thanks.
Pasi

7

post your full query

8

sorry, The full query is complex and about 2 -3 page, the sub query is what I posted.I think I figured it out, using it as:
select
column1,
column2,
column3,
------------- sub query.-----

,( select
total_gross =CASE
when erncode ='5' then 'Sal'
when erncode='55' then 'OclLump'
when erncode='35' then 'Promo'
when erncode='38' then 'Lump'
when erncode='39' then 'Reclass'
when erncode='40' then ' LatT'
end
from Employee)

From
personal

9

Hi ..

I think it's possible to include both columns in case statement

Only thing is
Levels

The columns that you are using
Must be in outer ring

Example

Select name ,age ,max
From
(
Select id , books from table 1 )

U cannot do case for name age max
In
Select id part

Hope you get my point

U can use case of id books
In
Top
Select name age max part

Please let me know if you understand
Or
I can make it really easy for you to
Understand by giving
Better .. picture

10

Thanks for the tips harrishgg1, I understand.
Pasi.