[firebase-br] invalid request BLR at offset 366

ronaldo em grupolta.com.br ronaldo em grupolta.com.br
Sex Set 5 17:03:46 -03 2008


Utilizo o firebird 1.5
A ultima linha dos where que contem "B.TIPO_OPERACAO =" funcionam até o 3 se 
eu tento rodar o 4 ele não funciona, da o tal erro.
Qualquer ordem de selecao que eu seguir para tentar rodar, quando passa de 3 
union não vai.

O comando é um pouco grande mas lá vai:

SELECT DISTINCT
       CAST('Troca de Oleo' AS VARCHAR(50)) AS OPERACAO,
       B.CODVEIC,
       C.MARCA,
       C.MODELO,
       (SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) AS ULTIMO_KM,
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) AS PRIMEIRO_KM,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1)) AS KM_RODADO,
--
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) AS QUANT,
       (SELECT
            SUM(D.VL_TOTAL)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) AS TOTAL,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1)) /
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 1) AS MEDIA_LTS_KM
FROM MOVFROTA_FMANUTENCAO A
 LEFT JOIN MOVFROTA_MANUTENCAO B ON ( B.CODMANUT = A.CODMANUT )
 LEFT JOIN VEICULOS C ON ( C.CODVEIC = B.CODVEIC )
WHERE A.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
  AND B.TIPO_OPERACAO = 1

UNION ALL

SELECT DISTINCT
       CAST('Rodizio de Pneu' AS VARCHAR(50)) AS OPERACAO,
       B.CODVEIC,
       C.MARCA,
       C.MODELO,
       (SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) AS ULTIMO_KM,
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) AS PRIMEIRO_KM,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2)) AS KM_RODADO,
--
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) AS QUANT,
       (SELECT
            SUM(D.VL_TOTAL)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) AS TOTAL,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2)) /
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 2) AS MEDIA_LTS_KM
FROM MOVFROTA_FMANUTENCAO A
 LEFT JOIN MOVFROTA_MANUTENCAO B ON ( B.CODMANUT = A.CODMANUT )
 LEFT JOIN VEICULOS C ON ( C.CODVEIC = B.CODVEIC )
WHERE A.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
  AND B.TIPO_OPERACAO = 2

UNION ALL

SELECT DISTINCT
       CAST('Troca Filtro Oleo' AS VARCHAR(50)) AS OPERACAO,
       B.CODVEIC,
       C.MARCA,
       C.MODELO,
       (SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) AS ULTIMO_KM,
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) AS PRIMEIRO_KM,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3)) AS KM_RODADO,
--
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) AS QUANT,
       (SELECT
            SUM(D.VL_TOTAL)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) AS TOTAL,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3)) /
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 3) AS MEDIA_LTS_KM
FROM MOVFROTA_FMANUTENCAO A
 LEFT JOIN MOVFROTA_MANUTENCAO B ON ( B.CODMANUT = A.CODMANUT )
 LEFT JOIN VEICULOS C ON ( C.CODVEIC = B.CODVEIC )
WHERE A.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
  AND B.TIPO_OPERACAO = 3

UNION ALL

SELECT DISTINCT
       CAST('Troca Filtro Combustivel' AS VARCHAR(50)) AS OPERACAO,
       B.CODVEIC,
       C.MARCA,
       C.MODELO,
       (SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) AS ULTIMO_KM,
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) AS PRIMEIRO_KM,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4)) AS KM_RODADO,
--
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) AS QUANT,
       (SELECT
            SUM(D.VL_TOTAL)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) AS TOTAL,
--
       ((SELECT
            MAX(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) -
       (SELECT
            MIN(D.KM_MANUT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODFMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4)) /
       (SELECT
            SUM(D.QUANT)
         FROM MOVFROTA_FMANUTENCAO D
         LEFT JOIN MOVFROTA_MANUTENCAO E ON ( E.CODMANUT = D.CODMANUT )
         LEFT JOIN VEICULOS F ON ( F.CODVEIC = E.CODVEIC )
        WHERE D.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
          AND E.CODVEIC = C.CODVEIC
          AND E.TIPO_OPERACAO = 4) AS MEDIA_LTS_KM
FROM MOVFROTA_FMANUTENCAO A
 LEFT JOIN MOVFROTA_MANUTENCAO B ON ( B.CODMANUT = A.CODMANUT )
 LEFT JOIN VEICULOS C ON ( C.CODVEIC = B.CODVEIC )
WHERE A.DATA_MANUT BETWEEN '01.08.2008' AND '30.08.2008'
  AND B.TIPO_OPERACAO = 4



----- Original Message ----- 
From: "Adriano dos Santos Fernandes" <adrianosf em uol.com.br>
To: <lista em firebase.com.br>
Sent: Friday, September 05, 2008 4:46 PM
Subject: Re: [firebase-br] invalid request BLR at offset 366


ronaldo-DX7emcwGKcI39yzSjRtAkw em public.gmane.org escreveu:
> Pessoal,
>
> Tenho varios selects que são unidos em uma unica consulta atravéz de 
> union, mas quando eu seleciono mais de 3 selects e tento rodar, me retorna 
> o seguinte erro:
>
> invalid request BLR at offset 366.
> context already in use (BLR error
>
> Alguém poderia me ajudar?
>
Isso é bug do FB... Eu corrigi alguns recentemente mas são relacionados
a COALESCE e CASE.

Passe o seu comando completo. Pode ser um bug desconhecido.


Adriano


______________________________________________
FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
Para saber como gerenciar/excluir seu cadastro na lista, use: 
http://www.firebase.com.br/fb/artigo.php?id=1107
Para consultar mensagens antigas: http://firebase.com.br/pesquisa 





Mais detalhes sobre a lista de discussão lista