SELECT muy complejo

[[duilioisola]]

Yo hice las pruebas con esto:

Código SQL [-]
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^

/*
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).


Solucion
--------
select count(*),
       case
         when iban = '' then 'Cuenta Vacia'
         when iban <> '' and p.codigo <> 'PD' then 'Cta. correcta'
         when iban <> '' and p.codigo is null then 'Pais erroneo'
         when iban <> '' and p.codigo not in ('ES', 'PD') then 'Cuentas Extranjero'
         else 'Error desconocido'
       end as tipcue
from ls01 l
left join pais p on substring(l.iban from 1 for 2) = p.codigo
where
l.forpago = :forpago
group by 2
*/