Club Delphi  
    FTP   CCD     Buscar   Trucos   Trabajo   Foros

Retroceder   Foros Club Delphi > Bases de datos > Firebird e Interbase
Registrarse FAQ Miembros Calendario Guía de estilo Buscar Temas de Hoy Marcar Foros Como Leídos

Respuesta
 
Herramientas Buscar en Tema Desplegado
  #1  
Antiguo 22-08-2018
Avatar de Angel.Matilla
Angel.Matilla Angel.Matilla is offline
Miembro
 
Registrado: ene 2007
Posts: 1.350
Poder: 19
Angel.Matilla Va por buen camino
SELECT muy complejo

Necesito hacer un SELECT pero la forma que se me ha ocurrido creo que es demasiado compleja y por lo tanto lenta; funciona bien pero quisiera, a ser posible, agilizar la ejecución del query. Ahora tengo esto, aunque es un poco monstruoso:
Código PHP:
SELECT 1COUNT(*), 'Sin cuenta' 
  
FROM Ls01 
 WHERE ForPago 
= :ForPago 
   
AND Iban '' 
 
UNION
SELECT 2
COUNT(*), 'Ctas. correctas' 
  
FROM Ls01 
 WHERE ForPago 
= :ForPago 
   
AND Iban <> '' 
   
AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'
 
UNION 
SELECT 3
COUNT(*), 'Ctas. erróneas' 
  
FROM Ls01 
 WHERE ForPago 
=  :ForPago 
   
AND Iban <> '' 
   
AND SUBSTRING(Iban FROM 1 FOR 2NOT IN (SELECT Codigo FROM Pais
 
UNION 
SELECT 4
COUNT(*), 'Ctas. extranjero' 
  
FROM Ls01 
 WHERE ForPago 
= :ForPago 
   
AND Iban <> '' 
   
AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo NOT IN ('ES''PD')) 
    
ORDER BY 1 
</div>

Estaba pensando en hacer un SELECT...CASE, pero ninguna de las dos formas que he probado me funciona.
Este query:
Código PHP:
SELECT COUNT(*),
   CASE
     
WHEN Iban '' THEN 1
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'THEN 2
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2NOT IN (SELECT Codigo FROM PaisTHEN 3
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo NOT IN ('ES''PD')) THEN 4
   END 
AS TipCue
  FROM Ls01
 WHERE ForPago 
= :ForPago
 GROUP BY 2 
al tratar de ejecutarlo me da este error
Cita:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).
Y esta otra forma:
Código PHP:
SELECT COUNT(*)
  
FROM Ls01
 WHERE ForPago 
= :ForPago AND
   CASE
     
WHEN Iban '' THEN 1
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'THEN 2
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2NOT IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'THEN 3
     WHEN Iban 
<> '' AND SUBSTRING(Iban FROM 1 FOR 2IN (SELECT Codigo FROM Pais WHERE Codigo NOT IN ('ES''PD')) THEN 4
   END 

me devuelve una única fila con valor 1. ¿Se puede hacer lo que pretendo?
Responder Con Cita
  #2  
Antiguo 22-08-2018
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 32.021
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
Sería necesario la estructura de las tablas implicadas y lo que realmente quieres conseguir.
Lectura obligada

Última edición por Casimiro Notevi fecha: 22-08-2018 a las 12:17:53.
Responder Con Cita
  #3  
Antiguo 22-08-2018
Avatar de Angel.Matilla
Angel.Matilla Angel.Matilla is offline
Miembro
 
Registrado: ene 2007
Posts: 1.350
Poder: 19
Angel.Matilla Va por buen camino
Cita:
Empezado por Casimiro Notevi Ver Mensaje
Sería necesario la estructura de las tablas implicadas y lo que realmente quieres conseguir.
Lo siento. La tabla es una vista y hay dos campos: Iban, que contiene el código IBAN de las cuentas, y Cuenta, que contiene especiificamente el número de cuenta. Además hay otra tabla Pais con los códigos ISO definidos para cada país más uno "fantasma" que me he creado yo (PD) para país desconocido, por si acaso. ForPago contiene la forma de pago.

Básicamente lo que necesito es saber cuantas filas de esa vista cumplen cada una de las cuatro condiciones:
  1. Sin cuenta: Iban y cuenta vacíos
  2. Cuentas correctas: El código de país del IBAN está en la tabla País
  3. Cuentas erróneas: El código de país del IBAN no está en la tabla País o es PD o está vacío
  4. Cuentas de extrajero: La cuenta es correcta pero el país no es España.
Ya digo que ahora mismo, con el SELECT...UNION que ponía al principio, funciona bien pero es muy lento.
Responder Con Cita
  #4  
Antiguo 22-08-2018
Avatar de duilioisola
[duilioisola] duilioisola is offline
Miembro Premium
 
Registrado: ago 2007
Ubicación: Barcelona, España
Posts: 1.732
Poder: 20
duilioisola Es un diamante en brutoduilioisola Es un diamante en brutoduilioisola Es un diamante en bruto
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
Responder Con Cita
  #5  
Antiguo 22-08-2018
Avatar de mamcx
mamcx mamcx is offline
Moderador
 
Registrado: sep 2004
Ubicación: Medellín - Colombia
Posts: 3.911
Poder: 25
mamcx Tiene un aura espectacularmamcx Tiene un aura espectacularmamcx Tiene un aura espectacular
Y que version del motor es? Y como es la(s) tabla de origen? con datos, pls.

Puedes poner en http://sqlfiddle.com/
__________________
El malabarista.

Última edición por mamcx fecha: 22-08-2018 a las 18:27:06.
Responder Con Cita
  #6  
Antiguo 23-08-2018
Avatar de Angel.Matilla
Angel.Matilla Angel.Matilla is offline
Miembro
 
Registrado: ene 2007
Posts: 1.350
Poder: 19
Angel.Matilla Va por buen camino
Cita:
Empezado por mamcx Ver Mensaje
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.

Cita:
Empezado por duilioisola Ver Mensaje
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.
Responder Con Cita
  #7  
Antiguo 23-08-2018
Avatar de duilioisola
[duilioisola] duilioisola is offline
Miembro Premium
 
Registrado: ago 2007
Ubicación: Barcelona, España
Posts: 1.732
Poder: 20
duilioisola Es un diamante en brutoduilioisola Es un diamante en brutoduilioisola Es un diamante en bruto
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
*/
Responder Con Cita
  #8  
Antiguo 23-08-2018
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 32.021
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
Buen trabajo
Responder Con Cita
  #9  
Antiguo 23-08-2018
Avatar de duilioisola
[duilioisola] duilioisola is offline
Miembro Premium
 
Registrado: ago 2007
Ubicación: Barcelona, España
Posts: 1.732
Poder: 20
duilioisola Es un diamante en brutoduilioisola Es un diamante en brutoduilioisola Es un diamante en bruto
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
Responder Con Cita
  #10  
Antiguo 23-08-2018
Avatar de duilioisola
[duilioisola] duilioisola is offline
Miembro Premium
 
Registrado: ago 2007
Ubicación: Barcelona, España
Posts: 1.732
Poder: 20
duilioisola Es un diamante en brutoduilioisola Es un diamante en brutoduilioisola Es un diamante en bruto
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
Responder Con Cita
  #11  
Antiguo 23-08-2018
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 32.021
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
Responder Con Cita
  #12  
Antiguo 23-08-2018
Avatar de mamcx
mamcx mamcx is offline
Moderador
 
Registrado: sep 2004
Ubicación: Medellín - Colombia
Posts: 3.911
Poder: 25
mamcx Tiene un aura espectacularmamcx Tiene un aura espectacularmamcx Tiene un aura espectacular
Cita:
Empezado por Angel.Matilla Ver Mensaje
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".
__________________
El malabarista.
Responder Con Cita
Respuesta


Herramientas Buscar en Tema
Buscar en Tema:

Búsqueda Avanzada
Desplegado

Normas de Publicación
no Puedes crear nuevos temas
no Puedes responder a temas
no Puedes adjuntar archivos
no Puedes editar tus mensajes

El código vB está habilitado
Las caritas están habilitado
Código [IMG] está habilitado
Código HTML está deshabilitado
Saltar a Foro

Temas Similares
Tema Autor Foro Respuestas Último mensaje
Problema matemático complejo DarkDudae Varios 4 21-03-2013 16:16:21
Buscador PHP, un poco mas complejo santiago14 PHP 4 25-08-2010 02:42:45
Manejo complejo de Strings MaMu OOP 5 14-10-2008 06:37:29
Imprimir un informe complejo Alexis7788 Impresión 1 02-11-2006 07:00:57
Como realizar un select complejo Nelly SQL 7 14-12-2005 02:14:53


La franja horaria es GMT +2. Ahora son las 15:13:44.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Traducción al castellano por el equipo de moderadores del Club Delphi
Copyright 1996-2007 Club Delphi