IF statement within CASE

[ssv45324]

Is it possible to use a if statement within case something like

select user.f_name,
CASE user.l_name
WHEN 'xxxxx' THEN
IF (user.user_id) in (select vendorcompany.user_id from user, vendorcompany where vendorcompany.company_id = user.company_id)
BEGIN
user.l_name
END
ELSE user.full_name
from user where user.user_id > 0

 

[SQLSister]

No, if statements cannot be used inside a select. You can however use a case within a case or make the case have three parts.

YOu also might consider left joining to the statement in the if statment as a derived table when you that.

Questions about posting. See faq183-874

 

[Catadmin]

I think the best thing to do here is the following:

Declare @UserLName int

Set @UserLName = isnull((select vendorcompany.user_id from user, 
vendorcompany 
where vendorcompany.company_id = user.company_id), 999999)
--999999 is a dummy value

select user.f_name, 
CASE WHEN @UserLName = 999999 THEN user.full_name     
ELSE user.l_name  
from user where user.user_id > 0

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

wouldn't this do what you wanted???

I assume you don't want to display the 'xxxxx'

select user.f_name,
COALESCE(u.full_name,REPLACE(u.l_name,'xxxxx',null))
from user u
where u.user_id > 0

I mean your code doesn't make any sense
If l_name = 'XXXXX'
IF the user_id is in the vendorcompany table display 'xxxxx' ???

or maybe this, then you need to check for null in v.user_id
select user.f_name,
CASE v.user_id WHEN NULL THEN 'SomeThingHere' ELSE COALESCE(u.full_name,REPLACE(u.l_name,'xxxxx',null)) END
from user u left join vendorcompany v on u.user_id = v.user_id
where u.user_id > 0


without data it's hard to say, please provide DDL script and DML statements if you want help

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Catadmin]

Ack! My above code is wrong. The SET statement for the variable doesn't include WHERE clauses which set a unique value to the variable.

Before I redo it, though, Denis makes a good point. What data do you have and what are you trying to accomplish.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ssv45324]

Thanks guys for the feedback. Your suggestions helped me solve the problem