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 1, COUNT(*), '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 2) IN (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 2) NOT IN (SELECT Codigo FROM Pais)
UNION
SELECT 4, COUNT(*), 'Ctas. extranjero'
FROM Ls01
WHERE ForPago = :ForPago
AND Iban <> ''
AND SUBSTRING(Iban FROM 1 FOR 2) IN (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 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
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 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 WHERE Codigo <> 'PD') 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 = 1
me devuelve una única fila con valor 1. ¿Se puede hacer lo que pretendo?