Aggregate error whe there is none
---------------------------------
Key: CORE-5901
URL: http://tracker.firebirdsql.org/browse/CORE-5901
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.8
Environment: Windows 64bit
Reporter: Duilio Juan Isola
Priority: Minor
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^
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
