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
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.
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
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