470,647 Members | 1,248 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

How to get the status?

I have a table with columns c1, c2, c3, c4.

if all nulls or blanks. Status = 0
if c1 assigned but no c2, c3, and c4, then status = 1
if c2 assigned but no c3 and c4, then status = 2
if c3 .. then ..
if c4 .. then ..

I want to have one SQL to get the status like (ignored checking for
blanks here for demo)

SELECT Status = (if not c4 is null then 4
else not c3 is null then 3
else not c2 is null then 2
else not c1 is null then 1
else 0)
FROM mytable.

Thought of using CASE ... WHEN ... but it is only on one colum.

Any better idea.

Thanks

John

Jul 23 '05 #1
1 999
I'm not sure why you believe that CASE can only reference a single
column (admittedly, Books Online shows only single-column examples):

select case
when coalesce(c1,c2,c3,c4) is null then 0
when c1 is not null and c2 is null and c3 is null and c4 is null then
1
when c1 is null and c2 is not null and c3 is null and c4 is null then
2
/* Add other combinations here */
else null end as 'Status'
from
(select null as 'c1', null as 'c2', null as 'c3', null as 'c4'
union all
select 1, null, null, null
union all
select null, 2, null, null) dt

Simon

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by mark4asp | last post: by
2 posts views Thread by Charles Mendell | last post: by
6 posts views Thread by Ada | last post: by
19 posts views Thread by Frances | last post: by
reply views Thread by Edwin.Madari | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.