Order By Statement, help please

joycesolomon · Aug 24, 2011

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 · Aug 24, 2011

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 
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 · Aug 24, 2011

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 · Aug 25, 2011

thanks i got this figured out

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!

Order By Statement, help please

joycesolomon

MIS

fredericofonseca

IS-IT--Management

JarlH

Programmer

joycesolomon

MIS

Similar threads

Part and Inventory Search

Sponsor