SELECT muy complejo

[[duilioisola]]

Supongo que es un fallo de Firebird... el problema está en los select de pais dentro del WHEN.
Si lo sustituyes por "IN ('PD', 'ES', ...)" funcinoa correctamente.
Para evitar esto selects, lo he unido a pais mediante LEFT JOIN

A diferencia del UNION, solo te saldrá un error por cada cuenta. Con el UNION podría aparecer dentro de dos registros distintos.
En el caso del CASE, por ejemplo nunca dirá que es una cuenta de Extranjero porque ya devuelve un registro como "Cuenta correcta".

He agregado un ELSE, por si no entra en ninguna condición...

Prueba con lo siguiente:

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

[mamcx]

Y que version del motor es? Y como es la(s) tabla de origen? con datos, pls.

Puedes poner en http://sqlfiddle.com/

[[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
*/

[Casimiro Noteví]

Buen trabajo

[[duilioisola]]

Por si le sirve a alquien:

Verificador de IBAN para Firebird

Código SQL [-]
create or alter procedure verificar_iban (
    iban_a_verificar varchar(100))
returns (
    valido smallint,
    iban varchar(34))
as
declare variable i integer;
declare variable l integer;
declare variable alpha varchar(34);
declare variable numero integer;
begin
    /*
       Devuelve el IBAN limpio (sin espacios ni simbolos).
       Devuelve VALIDO=1 si es correcto.
    */

    -- Inicializo resultado
    valido = 0;
    iban = '';

    -- Limpieza de cuenta (solo ['A'..'Z', '0'..'9'])
    iban_a_verificar = upper(iban_a_verificar);
    i = 1;
    while (i <= char_length(iban_a_verificar)) do
    begin
        if ((substring(iban_a_verificar from i for 1) in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
                                                          'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                                                          'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3',
                                                          '4', '5', '6', '7', '8', '9'))) then
            iban = iban || substring(iban_a_verificar from i for 1);

        i = i + 1;
    end

    -- Primero el caso obvio de que venga vacio
    if (iban <> '') then
    begin
        -- Paso los primeros 4 digitos al final (Pais + Control)
        iban = substring(iban from 5 for char_length(iban) - 4) || substring(iban from 1 for 4);

        -- Convierto letras a digitos A -> 10, B -> 11, C -> 12, ...
        i = 1;
        while (i <= char_length(iban)) do
        begin
            if (substring(iban from i for 1) = 'A') then
                iban = substring(iban from 1 for i - 1) || '10' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'B') then
                iban = substring(iban from 1 for i - 1) || '11' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'C') then
                iban = substring(iban from 1 for i - 1) || '12' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'D') then
                iban = substring(iban from 1 for i - 1) || '13' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'E') then
                iban = substring(iban from 1 for i - 1) || '14' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'F') then
                iban = substring(iban from 1 for i - 1) || '15' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'G') then
                iban = substring(iban from 1 for i - 1) || '16' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'H') then
                iban = substring(iban from 1 for i - 1) || '17' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'I') then
                iban = substring(iban from 1 for i - 1) || '18' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'J') then
                iban = substring(iban from 1 for i - 1) || '19' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'K') then
                iban = substring(iban from 1 for i - 1) || '20' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'L') then
                iban = substring(iban from 1 for i - 1) || '21' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'M') then
                iban = substring(iban from 1 for i - 1) || '22' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'N') then
                iban = substring(iban from 1 for i - 1) || '23' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'O') then
                iban = substring(iban from 1 for i - 1) || '24' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'P') then
                iban = substring(iban from 1 for i - 1) || '25' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Q') then
                iban = substring(iban from 1 for i - 1) || '26' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'R') then
                iban = substring(iban from 1 for i - 1) || '27' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'S') then
                iban = substring(iban from 1 for i - 1) || '28' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'T') then
                iban = substring(iban from 1 for i - 1) || '29' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'U') then
                iban = substring(iban from 1 for i - 1) || '30' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'V') then
                iban = substring(iban from 1 for i - 1) || '31' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'W') then
                iban = substring(iban from 1 for i - 1) || '32' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'X') then
                iban = substring(iban from 1 for i - 1) || '33' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Y') then
                iban = substring(iban from 1 for i - 1) || '34' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Z') then
                iban = substring(iban from 1 for i - 1) || '35' || substring(iban from i + 1 for char_length(iban) - i);

            i = i + 1;
        end

        -- Calculo de modulo 97 sobre la cuenta
        i = 1;
        l = 9;
        valido = 0;
        alpha = '';

        while (i <= char_length(iban)) do
        begin
            if (l > char_length(iban)) then
                l = char_length(iban);
            alpha = alpha || substring(iban from i for l);
            numero = cast(alpha as integer);
            valido = mod(numero, 97);
            i = i + l;
            alpha = cast(valido as varchar(34));
            l = 9 - char_length(alpha);
        end

        -- Valido si el resultado es 1
        if (valido <> 1) then
            valido = 0;
    end

    suspend;
end

[[duilioisola]]

Puedes verificar la tabla así:

Código SQL [-]
SELECT L.FORPAGO, L.IBAN,
       (SELECT VALIDO
        FROM VERIFICAR_IBAN(L.IBAN)) AS VALIDO
FROM LS01 L
ORDER BY L.IBAN

[Casimiro Noteví]

[Angel.Matilla]

Empezado por mamcx

Y que version del motor es? Y como es la(s) tabla de origen? con datos, pls.

Perdón, perdón... El motor es FB 2.5 y lo de subir la tabla con datos, no por seguridad. Lo siento. Los campos que me interesan son los que he descrito antes.

Empezado por duilioisola

Supongo que es un fallo de Firebird... el problema está en los select de pais dentro del WHEN.
Si lo sustituyes por "IN ('PD', 'ES', ...)" funcinoa correctamente.

Muchas gracias. Aparentemente funciona a la perfección y por supuesto tarda muchísimo menos.

[mamcx]

Empezado por Angel.Matilla

lo de subir la tabla con datos, no por seguridad. Lo siento. Los campos que me interesan son los que he descrito antes.

No tienes que poner datos reales, solo representativos. Puedes usar cualquier generador de datos:

https://duckduckgo.com/?q=data+generator&ia=web

Tambien es bueno mencionar tamaños de filas y el query planer, para saber que es lo "lento".