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
Comments
Modified by: Duilio Juan Isola (duilioisola)summary: Aggregate error whe there is none => Aggregate error when there is none |
Commented by: Omacht András (aomacht) Hi! I suggest you use (not) exists instead of (not) in. select x.TipCue, count(1) from ( András |
Commented by: Duilio Juan Isola (duilioisola) Than you András! I tryed that but it didn´t work either. SELECT COUNT(*), 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. |
Commented by: Omacht András (aomacht) Hi! |
Commented by: Rafael Dipold (dipold) The simple case doesn't work in V3.0.4.32961 SELECT |
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" |
Commented by: @livius2 But here is "GROUP BY 2" and 2 is the whole case expression |
Commented by: @livius2 test on system tables this works -------------------------------------------------------------------------- SELECT this also works -------------------------------------------------------------------------- but merged together not |
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^
The text was updated successfully, but these errors were encountered: