[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
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
[firebird-support] Cannot install 2.5.4 on Linux Mint - gsec error
Hi, I am having problems getting 2.5.4 to install on Linux Mint 17.1 using the instructions at https://help.ubuntu.com/community/Firebird2.5 The installation starts ok, prompts for SYSDBA password, but then shows the following: # sudo apt-get install firebird2.5-super Processing triggers for ureadahead (0.100.0-16) ... Setting up firebird2.5-common-doc (2.5.4.26856.ds4-1ubuntu1) ... Setting up firebird2.5-common (2.5.4.26856.ds4-1ubuntu1) ... Setting up libfbclient2:i386 (2.5.4.26856.ds4-1ubuntu1) ... Setting up libib-util:i386 (2.5.4.26856.ds4-1ubuntu1) ... Setting up firebird2.5-server-common (2.5.4.26856.ds4-1ubuntu1) ... Setting up firebird2.5-super (2.5.4.26856.ds4-1ubuntu1) ... Created default security2.fdb * Starting Firebird 2.5 super server... [ OK ] use gsec -? to get help cannot attach to password database unable to open database Error setting new SYSDBA password Please reconfigure the firebird package to try again * Firebird 2.5 super server already running Processing triggers for libc-bin (2.19-0ubuntu6.3) ... Processing triggers for ureadahead (0.100.0-16) ... Looking at the SYSDBA.password file, this hasn't been updated. Running 'dpkg-reconfigure firebird2.5-super' or 'gsec' to set the password results in the same error. I have previously installed 2.5.3 onto the same OS (different machine) using the same instructions and that worked perfectly. # ls -l /var/lib/firebird/2.5/system/security2.fdb -rw-rw 1 firebird firebird 802816 May 8 10:31 /var/lib/firebird/2.5/system/security2.fdb # uname -a Linux LinuxFB25 3.13.0-37-generic #64-Ubuntu SMP Mon Sep 22 21:30:01 UTC 2014 i686 i686 i686 GNU/Linux # lsb_release -d Description:Linux Mint 17.1 Rebecca Thanks, Dave
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)),
[firebird-support] Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
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.
