Aggregate error when there is none [CORE5901]

[firebird-automations]

Submitted by: Duilio Juan Isola (duilioisola)

If I execute this SQL statemente it fails with an "aggregate error". The problem is in the 2nd to 4th WHEN line. (It trys to know if the fisrt 2 letters corresponds or not to the field CODIGO from table PAIS) If I replace the IN (SELECT ...) with IN ('ES', 'DE', 'RU', ...) then it works fine but it is not what I want.

SELECT COUNT(*), CASE WHEN (Iban = '') THEN 1 WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'))) THEN 2 WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) NOT IN (SELECT Codigo FROM Pais))) THEN 3 WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) IN (SELECT Codigo FROM Pais WHERE Codigo NOT IN ('ES', 'PD')))) THEN 4 END AS TipCue FROM Ls01 WHERE ForPago = :ForPago GROUP BY 2

can't format message 13:896 -- message file C:\WINDOWS\firebird.msg not found. 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).

This is a test structure where it fails.

set term ^;

CREATE TABLE LS01 ( IBAN VARCHAR(34), FORPAGO VARCHAR(3) ) ^ commit work^

CREATE INDEX LS01_IDX1 ON LS01 (IBAN) ^ commit work^

CREATE INDEX LS01_IDX2 ON LS01 (FORPAGO) ^ commit work^

CREATE TABLE PAIS ( CODIGO VARCHAR(2) NOT NULL, DESCRIPCION VARCHAR(100) ) ^ commit work^

ALTER TABLE PAIS ADD CONSTRAINT PK_PAIS PRIMARY KEY (CODIGO) ^ commit work^

INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('ES', 'Spain')^ INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('DE', 'Germany')^ INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('PD', 'Not a country')^ commit work^

INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('', 'C')^ INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('ES123456', 'C')^ INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('DE456789', 'C')^ INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('PD121212', 'C')^ INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('ZZ989898', 'C')^ commit work^

[firebird-automations]

Modified by: Duilio Juan Isola (duilioisola)

summary: Aggregate error whe there is none =\> Aggregate error when there is none

[firebird-automations]

Commented by: Omacht András (aomacht)

Hi!

I suggest you use (not) exists instead of (not) in.

select x.TipCue, count(1) from ( SELECT CASE WHEN (l.Iban = '') THEN 1 WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo <> 'PD' and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 2 WHEN (l.Iban <> '') and (not exists (SELECT 1 FROM Pais p where p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 3 WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo NOT IN ('ES', 'PD') and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 4 END AS TipCue FROM Ls01 l WHERE l.ForPago = :ForPago) x group by x.TipCue

András

[firebird-automations]

Commented by: Duilio Juan Isola (duilioisola)

Than you András!

I tryed that but it didn´t work either.

SELECT COUNT(*), CASE WHEN (l.Iban = '') THEN 1 WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo <> 'PD' and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 2 WHEN (l.Iban <> '') and (not exists (SELECT 1 FROM Pais p where p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 3 WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo NOT IN ('ES', 'PD') and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 4 END AS TipCue FROM Ls01 l WHERE l.ForPago = :ForPago GROUP BY 2

I solved my particular problem with another workarround, but the problem still exists.

You made a SELECT ... FROM (SELECT ...). It works, but it is a workarround, not the solution or explanation to the problem.

[firebird-automations]

Commented by: Omacht András (aomacht)

Hi! I know that I made a workaround. The group by problem still exists, I just wanted to show you a solution asap. András

[firebird-automations]

Commented by: Rafael Dipold (dipold)

The simple case doesn't work in V3.0.4.32961

SELECT COUNT(*), CASE WHEN (IBAN = '') THEN 1 WHEN (EXISTS(SELECT 1 FROM PAIS)) THEN 2 END FROM LS01 GROUP BY 2

[firebird-automations]

Commented by: @hvlad

The field "lban" is used in SELECT list (actually, in CASE expression) and it should be "contained in either an aggregate function or the GROUP BY clause"

[firebird-automations]

Commented by: @livius2

But here is "GROUP BY 2" and 2 is the whole case expression

[firebird-automations]

Commented by: @livius2

test on system tables

this works

--------------------------------------------------------------------------

SELECT COUNT(*), CASE WHEN (R.RDB$RELATION_ID IS NULL) THEN 1 END FROM RDB$DATABASE R GROUP BY 2

this also works

-------------------------------------------------------------------------- SELECT COUNT(*), CASE WHEN (SELECT 1 FROM RDB$DATABASE R2)=1 THEN 2
END FROM RDB$DATABASE R GROUP BY 2

but merged together not -------------------------------------------------------------------------- SELECT COUNT(*), CASE WHEN (R.RDB$RELATION_ID IS NULL) THEN 1 WHEN (SELECT 1 FROM RDB$DATABASE R2)=1 THEN 2
END FROM RDB$DATABASE R GROUP BY 2