Subject | Re: [firebird-support] Invalid request BLR at offset 230 context already in use (BLR error) |
---|---|
Author | Svein Erling Tysvær |
Post date | 2013-05-09T19:49:14Z |
>is it should be a Firebird bug ?I don't know, I've never tried a CASE (<subselect>) WHEN ... THEN CASE WHEN (<subselect>) THEN... But of course, getting a BLR error doesn't sound right, I think it either ought to work or give you a different error message. You may want to ask at firebird-devel or somewhere, but you will need to specify your exact Firebird version and preferrably include a simple way to reproduce your problem.
I think your problem may be easily circumvented by moving the subselect to the WHEN clause (although I haven't tried). Does the below VIEW definition work?
CREATE VIEW temp ( yesno )
AS
select
CASE
WHEN (SELECT FIRST 1 P.TIPO FROM PEDIDO P WHERE P.IDPEDIDO = A.IDPEDIDO) = 'P' THEN
case when exists(select 1 from CLIENTE_FORMAPGTO cf
inner join pedido p on p.idcliente = cf.IDCLIENTE
where p.idpedido = a.idpedido and cf.IDFORMAPGTO = a.idformapgto)
then '<YES>' ELSE '<NO>' END
WHEN (SELECT FIRST 1 P.TIPO FROM PEDIDO P WHERE P.IDPEDIDO = A.IDPEDIDO) = 'V' then
case when exists(select 1 from FORMAPGTO f WHERE F.CKB1 = 'T'
and f.IDFORMAPGTO = a.idformapgto)
then '<YES>' ELSE '<NO>' END
ELSE
NULL
END
FROM PRESTACAO_PEDIDO a;
Alternatively, you could try (this may be slow if PEDIDO has millions of records or not be what you desire if you want a random value rather than a fixed value for TIPO):
CREATE VIEW temp ( yesno )
AS
WITH tmp (IDPEDIDO, TIPO) AS
(SELECT IDPEDIDO, MIN(TIPO) FROM PEDIDO GROUP BY 1)
SELECT a.IDFORMAPGTO,
CASE t.TIPO
WHEN 'P' THEN
CASE WHEN EXISTS(SELECT 1 FROM CLIENTE_FORMAPGTO cf
INNER JOIN PEDIDO p ON p.IDCLIENTE = cf.IDCLIENTE
WHERE p.IDPEDIDO = a.IDPEDIDO
AND cf.IDFORMAPGTO = a.IDFORMAPGTO) THEN
'<YES>' ELSE '<NO>' END
WHEN 'V' THEN
CASE WHEN EXISTS(SELECT 1 FROM FORMAPGTO f WHERE F.CKB1 = 'T'
AND f.IDFORMAPGTO = a.IDFORMAPGTO) THEN
'<YES>' ELSE '<NO>' END
ELSE
NULL
END
FROM PRESTACAO_PEDIDO a
LEFT JOIN tmp t ON a.IDPEDIDO = t.IDPEDIDO;
HTH,
Set