evaluation on a select case column

Question

Post reply

evaluation on a select case column

Christopher Klein

SSCrazy

Points: 2648
More actions
April 5, 2007 at 9:07 am

#103334

I need to do a case evaluation on another case column.
sec_group is a derived column and doesnt exist in the table
select code1,item,sec_group = case
when acct_sec='foo' then 'group found'
else 'qwerty'
end,
sub_group = case
when group_sec='group found' then 'sub group process'
else group_sec
end
from testtable
is there anyway I can evaluate the sec_group column within the same select statement without having to resort to a #temptable ?
I'm thinking NOT but I'm not sure how to get around it.
Thanks,
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18859 More actions · Answer 1

You can use a derived table:

SELECT *

FROM (

SELECT code1

,item

,sec_group =

CASE

WHEN acct_sec = 'foo'

THEN 'group found'

ELSE 'qwerty'

END

,sub_group =

CASE

WHEN group_sec='group found'

THEN 'sub group process'

ELSE group_sec

END

FROM testtable

) D

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

it still says that it is an invalid column name in the second case statement.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Only way that could happen is if group_sec doesn't exist... what do you get when you run the following?

SELECT COUNT(Group_Sec) FROM TestTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)