scottshr
01-24-2008, 08:36 AM
I am using the following query:
SELECT "Appt. Status" = CASE
WHEN (ApptStatus = 'T' AND Time_Out <> ' :') THEN 'Complete/Discharged'
WHEN (ApptStatus='T' AND Time_Out = ' :') THEN 'Checked in/In progress'
WHEN ApptStatus='A' THEN 'Upcoming'
WHEN ApptStatus='C' THEN 'Cancelled'
WHEN ApptStatus='N' then 'No Show'
ELSE 'Unknown' END, count(*)
FROM Systoc.SystocOwner.TimeAppt
WHERE (ApptDate = { fn Current_Date() })
GROUP by ApptStatus
I am getting:
There was an error executing the data source: There is an error in your SQL at line 1: Column 'Systoc.SystocOwner.TimeAppt.Time_Out' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Systoc.SystocOwner.TimeAppt.Time_Out' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I do not want the Time_Out field to be included in the group by because it should not be considered in grouping; it is merely part of a conditional statement. What can I do?
SELECT "Appt. Status" = CASE
WHEN (ApptStatus = 'T' AND Time_Out <> ' :') THEN 'Complete/Discharged'
WHEN (ApptStatus='T' AND Time_Out = ' :') THEN 'Checked in/In progress'
WHEN ApptStatus='A' THEN 'Upcoming'
WHEN ApptStatus='C' THEN 'Cancelled'
WHEN ApptStatus='N' then 'No Show'
ELSE 'Unknown' END, count(*)
FROM Systoc.SystocOwner.TimeAppt
WHERE (ApptDate = { fn Current_Date() })
GROUP by ApptStatus
I am getting:
There was an error executing the data source: There is an error in your SQL at line 1: Column 'Systoc.SystocOwner.TimeAppt.Time_Out' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Systoc.SystocOwner.TimeAppt.Time_Out' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I do not want the Time_Out field to be included in the group by because it should not be considered in grouping; it is merely part of a conditional statement. What can I do?