case statement - 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

General SQL Server Forums

New to SQL Server Programming

case statement

Author

Topic

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 10:08:59

I have a code, 744.9 that can be on a problem_list table or past_hx table. I'd like to have a column that shows "problem list" if it is in the problem_list table, "past hx" if it is in the past_hx table or "both" if it is both. How would I accomplish this? Case statement?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 10:13:40

select case
when pl.code = 744.9 then "problem list"
when hx.code = 744.9 then "past hx"
end
from problem_list pl
join past_hx hx
on 1=1

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 10:26:38

To have "both" appear would I do:
when pl.code and hx.code = 744.9 then "both"

quote:
Originally posted by gbritton

select case
when pl.code = 744.9 then "problem list"
when hx.code = 744.9 then "past hx"
end
from problem_list pl
join past_hx hx
on 1=1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 10:31:17

right!

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 10:43:39

How about if I wanted to have multiple codes? Like 744.9, 21.5, 36.8. Could I then create another column that shows with one is in the problem_list or past_hx?

code_column
21.5
744.9
location_column
both
problem_list

quote:
Originally posted by gbritton

right!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 10:53:15

use IN as:

select case
when pl.code IN (744.9, 21.5, 36.8) AND hx.code IN (744.9, 21.5, 36.8)
then "both"
when pl.code IN (744.9, 21.5, 36.8) then "problem list"
when hx.code IN (744.9, 21.5, 36.8) then "past hx"
end
from problem_list pl
join past_hx hx
on 1=1

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 11:00:31

Is there a way for me to know which code was found? Like if 744.9 in the problem_list could I have a column with 744.9 in it?
code found_in
744.9 problem_list

quote:
Originally posted by gbritton

use IN as:

select case
when pl.code IN (744.9, 21.5, 36.8) AND hx.code IN (744.9, 21.5, 36.8)
then "both"
when pl.code IN (744.9, 21.5, 36.8) then "problem list"
when hx.code IN (744.9, 21.5, 36.8) then "past hx"
end
from problem_list pl
join past_hx hx
on 1=1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 11:06:44

well sure, you'll need multiple case statements: one for each condition. Just build on what we have so far.

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 11:13:24

Sorry, not sure what you mean. Do I add a new case statement?

select case
when pl.code IN (744.9, 21.5, 36.8) then pl.code
when hx.code IN (744.9, 21.5, 36.8) then hx.code
end as code

quote:
Originally posted by gbritton

well sure, you'll need multiple case statements: one for each condition. Just build on what we have so far.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 11:47:04

several more, actually.


select case 
when pl.code IN (744.9, 21.5, 36.8) AND hx.code IN (744.9, 21.5, 36.8)
then "both"
when pl.code IN (744.9, 21.5, 36.8) then "problem list"
when hx.code IN (744.9, 21.5, 36.8) then "past hx"
end as where_found,
case when pl.code IN (744.9, 21.5, 36.8) then pl.code as code_in_problem_list,
case when hx.code IN (744.9, 21.5, 36.8) then hx.code as code_in_past_hx
-- ... etc. ...
from problem_list pl
join past_hx hx
on 1=1

tjonas
Starting Member

17 Posts

Posted - 2014-11-12 : 14:19:45

Thank you for the help.

quote:

Originally posted by gbritton

several more, actually.


select case 
when pl.code IN (744.9, 21.5, 36.8) AND hx.code IN (744.9, 21.5, 36.8)
then "both"
when pl.code IN (744.9, 21.5, 36.8) then "problem list"
when hx.code IN (744.9, 21.5, 36.8) then "past hx"
end as where_found,
case when pl.code IN (744.9, 21.5, 36.8) then pl.code as code_in_problem_list,
case when hx.code IN (744.9, 21.5, 36.8) then hx.code as code_in_past_hx
-- ... etc. ...
from problem_list pl
join past_hx hx
on 1=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources