The following query:
SELECT PB_LOAD_TYPE,
/* If already picked up use the pick up date.
If already delivered us the delivered date.
If appointment date present, use it.
Otherwise, use today's date */
IIF(OPS_ONBOARD_TIME IS NOT NULL,
CAST(OPS_ONBOARD_TIME AS DATE),
IIF(OPS_DELIVERED_TIME IS NOT NULL,
CAST(OPS_DELIVERED_TIME AS DATE),
COALESCE(CAST(OPS_APPT_LOW AS DATE), CAST('TODAY' AS DATE))))
AS APPT_DATE,
/* Get the region ID */
COALESCE((SELECT FIRST 1 CTRR_REGION_ID
FROM CT_REGION_RANGES
WHERE CTRR_LOW_ZIP <= OPS_ZIP AND
CTRR_HIGH_ZIP >= OPS_ZIP
ORDER BY CTRR_REGION_ID), 'N/A') AS REGION_ID,
/* Count pick ups */
SUM(IIF(OPS_TYPE = 'P', 1, 0)) AS PICKUPS,
/* Count deliveries */
SUM(IIF(OPS_TYPE = 'P', 0, 1)) AS DELIVERIES
FROM OPS_STOP_REC
LEFT JOIN OPS_HEADER ON PB_ID = OPS_ORDER_ID
WHERE PB_TYPE = 'O' AND
PB_DT_ENT >= :PB_DT_ENT
GROUP BY APPT_DATE, PB_LOAD_TYPE, REGION_ID
ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID
throws this error:
Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)
There is nothing wrong with the query that I can see. What am I missing?
If I remove the two SUM() lines and the GROUP BY it works perfectly.
I am using Firebird 2.5 32-bit on Windows 7 64-bit.
