Order By Statement, please help

[joycesolomon]

Hi I have the below order by statement


ORDER BY CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL
THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL
THEN [Last Name] ELSE [Last Name] + ',' + [First Name] END ELSE CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL
THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [First Name] + ',' + [Last Name] END

I keep getting error "incorrect syntax near the keyword "ELSE""
Can someone please coach me
thanks

 

[gmmastros]

Can you explain what you are trying to accomplish with this order by?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joycesolomon]

Here is something that i am trying to convert from the Access Query


Access Query:

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Contacts.*
FROM Contacts
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));



SQL Query:

SELECT TOP 100 PERCENT CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [Last Name] + ',' + [First Name] END END AS [File As], CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [First Name] + ',' + [Last Name] END END AS [Contact Name], dbo.Contacts.*
FROM dbo.Contacts
ORDER BY CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [Last Name] + ',' + [First Name] END ELSE CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [First Name] + ',' + [Last Name] END


This is a access template database called Task.accdb that i am trying to convert to sql database at the backend.

Thanks

 

[glenntb]

It appears that you've got two ELSE conditions tied to the first CASE statement. I copied your code into a text editor, and played with the line breaks to line up all the CASE WHEN and ELSE statements. Here's a pseudo-code picture of what your code looks like when you do this:

01 ORDER BY 
02	CASE WHEN ____ THEN 
03		CASE WHEN ____ THEN ____
04			ELSE ____ 
05		END 
06		ELSE CASE WHEN ____ THEN ____
07					ELSE ____
08				END 
09		ELSE CASE WHEN ____ THEN 
10					CASE WHEN ____ THEN ____
11						ELSE ____
12					END 
13					ELSE CASE WHEN ____ THEN ____
14								ELSE ____
15							END
16

Notice line 6 and 9... two ELSE statements tied to the CASE on line 2.

-Glenn

 

[joycesolomon]

Would this be a correct one then?

<code>

ORDER BY
CASE WHEN [Last Name] IS NULL THEN
CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END
ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [Last Name] + ',' + [First Name]
END
END
ELSE CASE WHEN [Last Name] IS NULL THEN
CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END
ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [First Name] + ',' + [Last Name]
END
END
END







</code>

 

[joycesolomon]

Glenn,

If i have two CASE statement like this, how to combine them?

<code>
CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL
THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL
THEN [Last Name] ELSE [Last Name] + ',' + [First Name] END
</code>

<code>
CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL
THEN [Company] ELSE [First Name] END ELSE CASE WHEN [First Name] IS NULL THEN [Last Name] ELSE [First Name] + ',' + [Last Name] END </code>

 

[joycesolomon]

Thanks all for the help!

I figured it out!