ORA-00904 VLPOREIXO. indentificador inválido - invalid colunn name -
Olá,
Estou batendo cabeça com um script, é referente ao valor de frete por quilometro, coloquei um case para o script calcular de acordo com a quilometragem. Mas para que esse script funcione o vlporeixo tem que calcular. Quando rodo o script da o erro ORA-00904 VLPOREIXO. indentificador inválido. Tem como calcular, alguem poderia me ajudar?
/* Formatted on 23-set-2019 16:13:20 (QP5 v5.126) */
SELECT /* MOTORISTA */
:inicio ||'' á ''|| :final as periodo,
c .dtsaida,
c.numcar,
c.codmotorista ||'' - ''||
e.nome motorista,
/* VEICULO */
c.codveiculo,
v.descricao,
v.placa,
v.marca,
v.antt,
v.codigorntrc,
v.qteixos,
/* CARREG. */
c.totpeso,
c.totvolume,
c.vltotal,
c.numnotas,
c.qtitens,
/* ROTA*/
c.codrotaprinc ||'' - ''|| r.descricao rota,
r.kmrota as rotaiv,
:adicional as adicional,
r.kmrota + :adicional as idaevolta,
(r.kmrota / 2) as distancia,
case when (r.kmrota+:adicional) between 1 and 100 then 2.19
when (r.kmrota+:adicional) between 101 and 200 then 1.35
when (r.kmrota+:adicional) between 201 and 300 then 1.18
when (r.kmrota+:adicional) between 301 and 400 then 1.11
when (r.kmrota+:adicional) between 401 and 500 then 1.07
when (r.kmrota+:adicional) between 501 and 600 then 1.04
when (r.kmrota+:adicional) between 601 and 700 then 1.02
when (r.kmrota+:adicional) between 701 and 800 then 1.01
when (r.kmrota+:adicional) between 801 and 900 then 1
when (r.kmrota+:adicional) between 901 and 1000 then 0.99
when (r.kmrota+:adicional) between 1001 and 1100 then 0.99
when (r.kmrota+:adicional) between 1101 and 1200 then 0.98
when (r.kmrota+:adicional) between 1201 and 1300 then 0.98
when (r.kmrota+:adicional) between 1301 and 1400 then 0.97
when (r.kmrota+:adicional) between 1401 and 1500 then 0.97
when (r.kmrota+:adicional) between 1501 and 1600 then 0.97
when (r.kmrota+:adicional) between 1601 and 1700 then 0.96
when (r.kmrota+:adicional) between 1701 and 1800 then 0.96
when (r.kmrota+:adicional) between 1801 and 1900 then 0.96
when (r.kmrota+:adicional) between 1901 and 2000 then 0.96
when (r.kmrota+:adicional) between 2001 and 2100 then 0.96
when (r.kmrota+:adicional) between 2101 and 2200 then 0.96
when (r.kmrota+:adicional) between 2201 and 2300 then 0.95
when (r.kmrota+:adicional) between 2301 and 2400 then 0.95
when (r.kmrota+:adicional) between 2401 and 2500 then 0.95
when (r.kmrota+:adicional) between 2501 and 2600 then 0.95
when (r.kmrota+:adicional) between 2601 and 2700 then 0.95
when (r.kmrota+:adicional) between 2701 and 2800 then 0.95
when (r.kmrota+:adicional) between 2801 and 2900 then 0.95
when (r.kmrota+:adicional) between 2901 and 3000 then 0.95
else 00
end as vlporeixo,
/* CALCULO*/
/*( (r.kmrota * r.valorcommot) * v.qteixos) pgtobruto,
e.fatorcomissao descontos,*/
( (r.kmrota * vlporeixo) * v.qteixos)
- ( ( (r.kmrota * vlporeixo) * v.qteixos)
* (e.fatorcomissao / 100))
liquido
FROM pccarreg c,
pcempr e,
pcveicul v,
pcrotaexp r
WHERE (c.dtsaida BETWEEN TO_DATE(''01/09/2019'',''dd/mm/yyyy'') AND TO_DATE(''30/09/2019'',''dd/mm/yyyy''))
--AND c.numcar in (:numcar)
AND c.codmotorista IN (3242)
AND c.codmotorista = e.matricula
AND c.codveiculo = v.codveiculo
AND c.codrotaprinc = r.codrota
AND c.codrotaprinc not in (1,2,3,4,40,309) /* Não incluir rotas da capital*/
order by c.codrotaprinc, c.numcar
Estou batendo cabeça com um script, é referente ao valor de frete por quilometro, coloquei um case para o script calcular de acordo com a quilometragem. Mas para que esse script funcione o vlporeixo tem que calcular. Quando rodo o script da o erro ORA-00904 VLPOREIXO. indentificador inválido. Tem como calcular, alguem poderia me ajudar?
/* Formatted on 23-set-2019 16:13:20 (QP5 v5.126) */
SELECT /* MOTORISTA */
:inicio ||'' á ''|| :final as periodo,
c .dtsaida,
c.numcar,
c.codmotorista ||'' - ''||
e.nome motorista,
/* VEICULO */
c.codveiculo,
v.descricao,
v.placa,
v.marca,
v.antt,
v.codigorntrc,
v.qteixos,
/* CARREG. */
c.totpeso,
c.totvolume,
c.vltotal,
c.numnotas,
c.qtitens,
/* ROTA*/
c.codrotaprinc ||'' - ''|| r.descricao rota,
r.kmrota as rotaiv,
:adicional as adicional,
r.kmrota + :adicional as idaevolta,
(r.kmrota / 2) as distancia,
case when (r.kmrota+:adicional) between 1 and 100 then 2.19
when (r.kmrota+:adicional) between 101 and 200 then 1.35
when (r.kmrota+:adicional) between 201 and 300 then 1.18
when (r.kmrota+:adicional) between 301 and 400 then 1.11
when (r.kmrota+:adicional) between 401 and 500 then 1.07
when (r.kmrota+:adicional) between 501 and 600 then 1.04
when (r.kmrota+:adicional) between 601 and 700 then 1.02
when (r.kmrota+:adicional) between 701 and 800 then 1.01
when (r.kmrota+:adicional) between 801 and 900 then 1
when (r.kmrota+:adicional) between 901 and 1000 then 0.99
when (r.kmrota+:adicional) between 1001 and 1100 then 0.99
when (r.kmrota+:adicional) between 1101 and 1200 then 0.98
when (r.kmrota+:adicional) between 1201 and 1300 then 0.98
when (r.kmrota+:adicional) between 1301 and 1400 then 0.97
when (r.kmrota+:adicional) between 1401 and 1500 then 0.97
when (r.kmrota+:adicional) between 1501 and 1600 then 0.97
when (r.kmrota+:adicional) between 1601 and 1700 then 0.96
when (r.kmrota+:adicional) between 1701 and 1800 then 0.96
when (r.kmrota+:adicional) between 1801 and 1900 then 0.96
when (r.kmrota+:adicional) between 1901 and 2000 then 0.96
when (r.kmrota+:adicional) between 2001 and 2100 then 0.96
when (r.kmrota+:adicional) between 2101 and 2200 then 0.96
when (r.kmrota+:adicional) between 2201 and 2300 then 0.95
when (r.kmrota+:adicional) between 2301 and 2400 then 0.95
when (r.kmrota+:adicional) between 2401 and 2500 then 0.95
when (r.kmrota+:adicional) between 2501 and 2600 then 0.95
when (r.kmrota+:adicional) between 2601 and 2700 then 0.95
when (r.kmrota+:adicional) between 2701 and 2800 then 0.95
when (r.kmrota+:adicional) between 2801 and 2900 then 0.95
when (r.kmrota+:adicional) between 2901 and 3000 then 0.95
else 00
end as vlporeixo,
/* CALCULO*/
/*( (r.kmrota * r.valorcommot) * v.qteixos) pgtobruto,
e.fatorcomissao descontos,*/
( (r.kmrota * vlporeixo) * v.qteixos)
- ( ( (r.kmrota * vlporeixo) * v.qteixos)
* (e.fatorcomissao / 100))
liquido
FROM pccarreg c,
pcempr e,
pcveicul v,
pcrotaexp r
WHERE (c.dtsaida BETWEEN TO_DATE(''01/09/2019'',''dd/mm/yyyy'') AND TO_DATE(''30/09/2019'',''dd/mm/yyyy''))
--AND c.numcar in (:numcar)
AND c.codmotorista IN (3242)
AND c.codmotorista = e.matricula
AND c.codveiculo = v.codveiculo
AND c.codrotaprinc = r.codrota
AND c.codrotaprinc not in (1,2,3,4,40,309) /* Não incluir rotas da capital*/
order by c.codrotaprinc, c.numcar
Rodrigo Palácio
Curtidas 1
Respostas
Fabiano Carvalho
28/10/2019
Voce está usando coluna VLPOREIXO para realizar o calculo mas ela não existe na tabela, só em tempo de consulta.
O seu case voce precisa colocar onde está usando a coluna VLPOREIXO ou cria uma view com a consulta e inclua o case que está usando.
O seu case voce precisa colocar onde está usando a coluna VLPOREIXO ou cria uma view com a consulta e inclua o case que está usando.
GOSTEI 0