Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Aggregate error when there is none [CORE5901] #6159

Open
firebird-automations opened this issue Aug 23, 2018 · 8 comments
Open

Aggregate error when there is none [CORE5901] #6159

firebird-automations opened this issue Aug 23, 2018 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: Duilio Juan Isola (duilioisola)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @livius2

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

@firebird-automations
Copy link
Collaborator Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant