Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nested Cases in SQL Query

Status
Not open for further replies.

tfhwargt3

Programmer
Sep 19, 2006
61
US
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]

Code:
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?
 
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.
 
For all of those who are wondering the solution is below:

Code:
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.
 
You have a missing CASE
e.g.
Code:
select 
case when 1=1 then 
  [COLOR=red]case[/color] when 2=2 then 
    'a'
  else 
    'b'
  end
else
  'c'
end
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top