07.05.2015 20:23, sboydlns@... wrote:

> 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 P ICKUPS,
>
> /* 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?

You cannot GROUP BY subselect, you should instead GROUP BY OPS_ZIP (its
link with the parent query).


Dmitry