[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