none
Using Case in Order by clause

    Question

  • How would I use the Case statement in the Order by clause in this query? The rest of the query works without the case statement. The DDLs are long, so I didn't post them. I think I just need help with the syntax. Let me know if you need the DDLs.

    :DBLookupComboBox1 is alphanumeric

    :DBLookupComboBox2 is numeric

    BL.ChargeName and TT.Doctor are varchar

    I've seen posts suggesting casting to numeric when using Case. I don't know if that is the issue here.

    The error message is "Incorrect syntax near ','"

    SELECT TT.DOCTOR, TP.Adatetime as 'Date of Service', TP.Patient_No, 
    (TP.Last_Name + ', '+ TP.First_name) as 'Client Name', CC_Loc.description as Location, 
    BL.ChargeName as Clinic, PS.Service_Name, PS.Service_Code, Convert(Int,REPLACE(PS.Service_Time, ',', ''))
     as 'Time Spent'
    FROM treat TT
    Inner Join typepatient TP on TT.ednum = TP.Ednum
    Inner Join Live_Knowledge.dbo.CRIS_LOCATION CC_Loc on TP.location_id = CC_Loc.id
    Left outer join Patient_Service PS On TP.Ednum = PS.Ednum
    left outer join Billing Bl on TP.ednum=BL.Ednum     
    Where TP.Adatetime >= convert(varchar(10), :ST, 121)+ ' 00:00:00.000'
    and TP.Adatetime <= convert(varchar(10), :SP, 121)+ ' 23:59:00.000'
             
    And TP.Sign = '1'  
    and TP.Visit_Type in ('Outpatient', 'Inpatient', 'Substance Evaluation', 'Emergency', 'Triage')
    and TP.Visit_Name <> 'Backfill'
     and (((:DBLookupComboBox2 = 0)  or (TT.Dr_ID =:DBLookupComboBox2)) and ((:DBLookupComboBox1 = 'QQZ') or (BL.ChargeName = :DBLookupComboBox1)))                         
    
    ORDER BY
    CASE WHEN :DBLookupComboBox1 = 'QQZ'
    AND :DBLookupComboBox2 <> 0
    THEN BL.ChargeName, TP.Date_ END asc,  
    CASE WHEN :DBLookupComboBox1 <> 'QQZ'
    THEN TT.DOCTOR, TP.Date_ END asc                         
    

    Wednesday, July 24, 2013 7:34 PM

Answers

  • ORDER BY
    CASE WHEN (:DBLookupComboBox1 = 'QQZ')
    THEN BL.ChargeName Else TT.Doctor                                    
    END asc, TP.Date_ 

    • Proposed as answer by Naomi NModerator Wednesday, July 24, 2013 10:04 PM
    • Marked as answer by DavidWIII Thursday, July 25, 2013 4:51 PM
    Wednesday, July 24, 2013 8:46 PM

All replies

  • SELECT columns FROM tables 
    WHERE condition 
    ORDER BY      
       case when Table1.Col1 IS NULL then 0 else 1 end ASC      
       ,case when Table1.Col1 IS NULL then Table2.Col2 else Table1.Col1 end DESC


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Wednesday, July 24, 2013 7:41 PM better
    • Proposed as answer by Prajesh Wednesday, July 24, 2013 10:10 PM
    Wednesday, July 24, 2013 7:40 PM
  • If I remove TP.Date from the Order by clause, the message is "Could not find object" instead of "Incorrect syntax near ','".

    ORDER BY
    CASE WHEN (:DBLookupComboBox1 = 'QQZ')  
    THEN BL.ChargeName END asc,  
    CASE WHEN (:DBLookupComboBox1 <> 'QQZ')  
    THEN TT.DOCTOR END asc
    Wednesday, July 24, 2013 8:05 PM
  • Thanks, Prajesh. I think I need a little more help.

    The error "Could not find object" is a quirk with the report writer that I fixed. The Order by clause below works now that I removed TP.Date. Does anyone know how to get it to order by (BL.ChargeName and TP.Date) or (TT.Doctor and TP.Date)? Do I need to cast?

    ORDER BY
    CASE WHEN (:DBLookupComboBox1 = 'QQZ')
    THEN BL.ChargeName Else TT.Doctor                                    
    END asc

    Wednesday, July 24, 2013 8:31 PM
  • ORDER BY
    CASE WHEN (:DBLookupComboBox1 = 'QQZ')
    THEN BL.ChargeName Else TT.Doctor                                    
    END asc, TP.Date_ 

    • Proposed as answer by Naomi NModerator Wednesday, July 24, 2013 10:04 PM
    • Marked as answer by DavidWIII Thursday, July 25, 2013 4:51 PM
    Wednesday, July 24, 2013 8:46 PM
  • Thanks. I tried Jingyang's solution and got it working before I even saw the reply. I'll try Prajesh's solution again later when I have more time.
    Thursday, July 25, 2013 4:55 PM
  • Thanks

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 5:01 PM