RE: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?
Arno, Thanks for your pointing out the issue with my SQL statement. Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, April 06, 2016 4:36 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL? Hi, The message doesn’t tell you which expression is not contained in an aggregate function or GROUP BY clause, but is very clear that it is “P.PHONE_ID” Not sure what you trying to achieve, but your SQL is indeed invalid, because there can be multiple PHONE_ID’s per AREA_CODE, PHONE_NO Kind Regards, Arno Brinkman From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, April 6, 2016 10:03 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL? SELECT DISTINCT P.AREA_CODE, P.PHONE_NO, (SELECT COUNT(*) FROM PHONE P2 WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT FROM PHONE P WHERE P.AREA_CODE IS NOT NULL GROUP BY 1, 2 HAVING (SELECT COUNT(*) FROM PHONE P3 WHERE P3.PHONE_ID = P.PHONE_ID AND P3.AREA_CODE IS NOT NULL) > 1 Dynamic SQL Error SQL error code = -104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) [Non-text portions of this message have been removed]
RE: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?
Tomasz, Thanks for your guidance and explanation for a much simpler solution. Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, April 07, 2016 2:58 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL? On 06.04.2016 o 22:03, 'stwizard' stwiz...@att.net [firebird-support] wrote: > SELECT DISTINCT P.AREA_CODE, P.PHONE_NO, > > (SELECT COUNT(*) > > FROM PHONE P2 > > WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT > > FROM PHONE P > > WHERE P.AREA_CODE IS NOT NULL > > GROUP BY 1, 2 [ ... ] select A, B, (select count(*) from ...) from ... is not the same as select A, B, count(*) from ... In the former case, the sub-select is not an aggregate function. It simply calculates and returns a scalar (a single number), which is treated just like one more "field" in selected records. Therefore, for the GROUP BY to work correctly, you'd have to include the third selected column (sub-select in your case) in the GROUP BY. But that's not what you wanted, I suppose. I guess what you really need is a normal grouped query: select AREA_CODE, PHONE_NO, count(*) from PHONE where AREA_CODE is not null group by 1, 2 having count(*) > 1 And that'll do the job. Best regards Tomasz [Non-text portions of this message have been removed]
Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?
On 06.04.2016 o 22:03, 'stwizard' stwiz...@att.net [firebird-support] wrote: > SELECT DISTINCT P.AREA_CODE, P.PHONE_NO, > >(SELECT COUNT(*) > > FROM PHONE P2 > > WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT > >FROM PHONE P > > WHERE P.AREA_CODE IS NOT NULL > > GROUP BY 1, 2 [ ... ] select A, B, (select count(*) from ...) from ... is not the same as select A, B, count(*) from ... In the former case, the sub-select is not an aggregate function. It simply calculates and returns a scalar (a single number), which is treated just like one more "field" in selected records. Therefore, for the GROUP BY to work correctly, you'd have to include the third selected column (sub-select in your case) in the GROUP BY. But that's not what you wanted, I suppose. I guess what you really need is a normal grouped query: select AREA_CODE, PHONE_NO, count(*) from PHONE where AREA_CODE is not null group by 1, 2 having count(*) > 1 And that'll do the job. Best regards Tomasz
Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?
Hi, The message doesn’t tell you which expression is not contained in an aggregate function or GROUP BY clause, but is very clear that it is “P.PHONE_ID” Not sure what you trying to achieve, but your SQL is indeed invalid, because there can be multiple PHONE_ID’s per AREA_CODE, PHONE_NO Kind Regards, Arno Brinkman From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, April 6, 2016 10:03 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL? SELECT DISTINCT P.AREA_CODE, P.PHONE_NO, (SELECT COUNT(*) FROM PHONE P2 WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT FROM PHONE P WHERE P.AREA_CODE IS NOT NULL GROUP BY 1, 2 HAVING (SELECT COUNT(*) FROM PHONE P3 WHERE P3.PHONE_ID = P.PHONE_ID AND P3.AREA_CODE IS NOT NULL) > 1 Dynamic SQL Error SQL error code = -104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
