[firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
07.05.2015 20:23, sboyd...@gmail.com 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
[firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
Except that grouping by OPS_ZIP would not accomplish the same thing. Multiple zip codes map to the same REGION_ID and the zip codes might not be contiguous. Guess I'm going to have to rethink this.
Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
>Except that grouping by OPS_ZIP would not accomplish the same thing. Multiple >zip codes map to the same REGION_ID and the zip codes might not be contiguous. > >Guess I'm going to have to rethink this. What about with tmp(PB_LOAD_TYPE, APPT_DATE, REGION_ID, PICKUP, DELIVERY) as (SELECT PB_LOAD_TYPE, CAST(COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW, 'TODAY') AS DATE), COALESCE((SELECT FIRST 1 CTRR_REGION_ID FROM CT_REGION_RANGES WHERE OPS_ZIP BETWEEN CTRR_LOW_ZIP AND CTRR_HIGH_ZIP ORDER BY CTRR_REGION_ID), 'N/A'), IIF(OPS_TYPE = 'P', 1, 0), IIF(OPS_TYPE = 'P', 0, 1) 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) SELECT PB_LOAD_TYPE, APPT_DATE, REGION_ID, sum(PICKUP) as PICKUPS, sum(DELIVERY) as DELIVERIES FROM tmp GROUP BY PB_LOAD_TYPE, APPT_DATE, REGION_ID ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID HTH, Set
Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
SET; That's slick. I'm going to have to read up on the WITH command. Had to change the coalesce with all the dates to cast each date individually or I got a conversion error. Not sure why. COALESCE(CAST(OPS_ONBOARD_TIME AS DATE), CAST(OPS_DELIVERED_TIME AS DATE), CAST(OPS_APPT_LOW AS DATE), CAST('TODAY' AS DATE)),
Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
Steve, At 08:54 a.m. 8/05/2015, sboyd...@gmail.com [firebird-support] wrote: >Had to change the coalesce with all the dates to cast each date individually >or I got a conversion error. Not sure why. > >COALESCE(CAST(OPS_ONBOARD_TIME AS DATE), CAST(OPS_DELIVERED_TIME AS DATE), >CAST(OPS_APPT_LOW AS DATE), CAST('TODAY' AS DATE)), Assuming OPS_ONBOARD_TIME, OPS_DELIVERED_TIME and OPS_APPT_LOW are all DATE type, does this solve that problem? COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW, CURRENT_DATE) Helen
Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
> Assuming OPS_ONBOARD_TIME, OPS_DELIVERED_TIME and OPS_APPT_LOW are all DATE > type,does this solve that problem? > COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW, CURRENT_DATE) Since they are all TIMESTAMPS, that didn't fix the problem but CURRENT_TIMESTAMP did. Thanks.