Multiple Selects in Case statement 2

[timroop]

Can you use a case statement to designate multiple fields in a select clause?
For example:

select employid, 
	case 
		when Plan_1 is not null
		then (Plan_1_Beg_date, Plan_1_End_Date)
		else Null, Null
	end

Rather than having a Case for Each field.

select employid,
        case 
          when Plan_1 is not null
             then Plan_1_Beg_date
             else Null
          end,
        case
          when Plan_1 is not null
             then Plan_1_End_Date
             else Null
          end

I just can't seem to get the code syntax and Books Online doesn't seem to address this.

Thanks.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[SQLSister]

I believe you need the case for each field.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[timroop]

SQLSister - Thank you for the verification.

That's kinda what I figured. I was just hoping for a single case evaluation rather than the six needed.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[SQLDenis]

doesn't work it tested it out in out favorite DB pubs

select case when country ='usa' then pub_name , city  else null end
 from publishers

1 way is to concatenate (1 field and you need to convert to varchar for datetime fields)

select case when country ='usa' then pub_name + ',' + city  else null end
 from publishers

or multiple case

select case when country ='usa' then pub_name  else null end as pub_name,
case when country ='usa' then city  else null end as city
 from publishers

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[timroop]

SQLDenis

I have used the technique for the Concatenate method. That is why I was approaching it for the Select issue too.

I'm going to just 'byte' the bullet and use multiple cases.

Thanks. (Stars for the effort)

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[mreigler]

How about a self join?

select A.employid, B.Plan_1_Beg_date, B.Plan_1_End_Date
	From A
	left join (select employid, plan_1_beg_date, plan_1_end_date FROM A WHERE plan1 IS NOT NULL) B 
	ON (B.employid = A.employid)

Mike Reigler
Melange Computer Services, Inc