Nested Cases in SQL Query

[tfhwargt3]

I am having a problem doing nested case statements within a select statement. The error I get is [red]"Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WHEN'.[/red]

SELECT 
Sales.OrderID, 
Buyers.eUserid, 
Buyers.Email, 
CASE
	WHEN Addresses.Company IS NULL OR Addresses.Company = '' THEN 
		WHEN RTRIM(LTRIM(Addresses.FirstName)) IS NULL THEN
			RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
		ELSE
			RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
ELSE
	Addresses.Company AS CompanyOrName,
END
...
FROM ...
...

I have tried using IF statements in the same manner but it seems these only work in stored procedures? Is this correct? Is it possible to do nested when's inside case statements? Can I use an IF Else statement if this is only a SQL QUERY and not a procedure?

 

[tfhwargt3]

Unbelievable...

As soon as I stepped away to take a break for a moment I figured it out. It seems that I will wait till I take a break next time to post a question.

 

[tfhwargt3]

For all of those who are wondering the solution is below:

COALESCE(a.Company, RTRIM(LTRIM(a.FirstName)) + ' ' + RTRIM(LTRIM(a.LastName)), RTRIM(LTRIM(a.LastName))) AS CompanyOrName,
[code]

The COALESCE function returns the first non-null arguement given.  So it worked perfectly in this situation.

 

[PDreyer]

You have a missing CASE
e.g.

select 
case when 1=1 then 
  [COLOR=red]case[/color] when 2=2 then 
    'a'
  else 
    'b'
  end
else
  'c'
end

 

[Qik3Coder]

FYI, for whoever comes across this later:

You can do the following:

Case
When X Then A
When b Then C
When d Then e
Else Z
END

This is legitimate syntax.

-Sometimes the answer to your question is the hack that works