Order By Statement, help please

[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 END,
CASE WHEN [Last Name] IS NULL THEN CASE WHEN [First Name] IS NULL THEN [Company] ELSE [First Name] END CASE WHEN [First Name] IS NULL
THEN [Last Name] ELSE [First Name] + ',' + [Last Name] END END


I keep getting error "incorrect syntax near the keyword "CASE""

Can someone please coach me

thanks

 

[fredericofonseca]

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,
CASE WHEN [Last Name] IS NULL 
     THEN 
          CASE WHEN [First Name] IS NULL 
               THEN [Company]
               ELSE [First Name] 
          END  <--- missing one else here probably
          CASE WHEN [First Name] IS NULL
               THEN [Last Name] ELSE [First Name] + ',' + [Last Name] 
          END 
END

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JarlH]

Just a small tip, when working with nested CASE's, add parentheses to make it easier to read and understand.

Also, the SQL Validator can be useful to find syntax errors:

http://developer.mimer.com/validator/parser200x/

(But then you'll have to use ANSI style [tt]"First Name"[/tt] instead of [tt][First Name][/tt], and [tt]||[/tt] instead of [tt]+[/tt].)

 

[joycesolomon]

thanks i got this figured out