Menu de navegação

[BrasilDisTIParticipante]

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 ou tem outro forma de funcionar?

/* 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

[José Laurindo ChiappaModerador]

Bom, ANTES DE TUDO eu tenho que apontar algumas coisas de SINTAXE, tais como : que o caracter / na linguagem PL/SQL absolutamente SÓ SERVE para indicar operação matemática de DIVISÃO (então essa linha SELECT / MOTORISTA / :inicio ) é puro LIXO na sintaxe PL/SQL, que as strings na linguagem SQL e na linguagem PL/SQL são identificadas por uma ASPA SIMPLES, assim PORTANTO então essas coisas de codrotaprinc ||’ – ‘|| OU de TO_DATE(’01/09/2019′,’dd/mm/yyyy’) não fazem Sentido algum na linguagem PL/SQL ou na liguagem SQL….

Isso posto, a sua resposta : não sei se vc sabe, mas as cláusulas WHERE e ORDER BY são executadas ** ANTES ** do SELECT : então, quando vc cria/inventa uma coluna no SELECT (seja com CASE, seja com um conta/expressão, seja como resultado de uma função, não importa), essa coluna INVENTADA só é conhecida no SELECT, E apenas dentro dessa coluna , o WHERE ** não ** a conhece, ele vai dar uma msg de IDENTIFICADOR INVÁLIDO se vc fazer isso, veja meu exemplo :

==> faço uma consulta com uma coluna ‘inventada’, que não está na tabela :

scott@DESENV:SQL>select deptno, empno, ename, case when deptno=10 then 'DEPTNO É 10' else 'Outro DEPTNO' end as DEPTO_10
  2* from emp ;

    DEPTNO      EMPNO ENAME      DEPTO_10
---------- ---------- ---------- ------------
        20       7369 SMITH      Outro DEPTNO
        30       7499 ALLEN      Outro DEPTNO
        30       7521 WARD       Outro DEPTNO
        20       7566 JONES      Outro DEPTNO
        30       7654 MARTIN     Outro DEPTNO
        30       7698 BLAKE      Outro DEPTNO
        10       7782 CLARK      DEPTNO É 10
        20       7788 SCOTT      Outro DEPTNO
        10       7839 KING       DEPTNO É 10
        30       7844 TURNER     Outro DEPTNO
        20       7876 ADAMS      Outro DEPTNO
        30       7900 JAMES      Outro DEPTNO
        20       7902 FORD       Outro DEPTNO
        10       7934 MILLER     DEPTNO É 10

14 linhas selecionadas.

==> Agora tento referenciar essa coluna no WHERE, simplesmente Não Funciona :

scott@DESENV:SQL>select deptno, empno, ename, case when deptno=10 then 'DEPTNO É 10' else 'Outro DEPTNO' end as DEPTO_10
  2  from emp
  3* where DEPTO_10 = 'DEPTNO É 10';

where DEPTO_10 = 'DEPTNO É 10'
      *
ERRO na linha 3:
ORA-00904: "DEPTO_10": invalid identifier

==>> IGUALMENTE, se eu quiser usar essa coluna inventada no MESMO SELECT onde ela foi definida, CLARO que não funciona também :

scott@DESENV:SQL>select deptno, empno, ename,
  2   case when deptno=10 then 'DEPTNO É 10' else 'Outro DEPTNO' end as DEPTO_10,
  3   DEPTO_10 || '!!'  
  4   from emp
  5  ;
 DEPTO_10 || '!!'
 *
ERRO na linha 3:
ORA-00904: "DEPTO_10": invalid identifier

==> A Solução é vc “MATERIALIZAR” a coluna inventada, fazendo ela se tornar PARTE FIXA do resultset usado no FROM : aí sim o WHERE vai poder a referenciar… Uma maneira de fazer isso é transformar o SELECT original numa sub-query no FROM, tipo :

scott@DESENV:SQL>SELECT * FROM (select deptno, empno, ename, case when deptno=10 then 'DEPTNO É 10' else 'Outro DEPTNO' end as DEPTO_10
  2                   from emp
  3                )
  4* where DEPTO_10 = 'DEPTNO É 10';

    DEPTNO      EMPNO ENAME      DEPTO_10
---------- ---------- ---------- ------------
        10       7782 CLARK      DEPTNO É 10
        10       7839 KING       DEPTNO É 10
        10       7934 MILLER     DEPTNO É 10

scott@DESENV:SQL>

==> AÍ SIM FUNCIONA, sim sim sim ??? Pelo Jeito é ISSO que vc não fez nesse seu SELECT, vc INVENTOU a coluna VLPOREIXO no SELECT ** E ** quer fazer contas com ela em outras colunas do mesmo SELECT : como eu disse, nesse SELECT mesmo onde a coluna inventada foi criada ela ainda não foi materializada, E também (da mesma forma) o WHERE desse SELECT, o ORDER BY desse select, o GROUP BY desse select, não a conhecem também…
UMA VEZ CORRIGIDOS os pontos de sintaxe que indiquei, acredito que a correção seria algo tipo :

SELECT 
    SUB.*,
   ((kmrota * valorcommot) * qteixos) pgtobruto,
    e.fatorcomissao descontos,
    ( (r.kmrota * vlporeixo) * v.qteixos) – ( ( (r.kmrota * vlporeixo) * v.qteixos) * (e.fatorcomissao / 100)) AS liquido
 FROM (SELECT /* MOTORISTA */ :inicio ||' á '|| :final as periodo,
      c .dtsaida,
      c.numcar,
      c.codmotorista ||' – '|| .....
      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
      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*/
             ) SUB
order by codrotaprinc, numcar

==> EVIDENTEMENTE, nem me preocupei em ver se ois parêntesis estão corretos (PROVAVELMENTE NÃO ESTÃO), e também parece ter uns comentários fora do lugar também, MAS a idéia é essa – fica POR SUA CONTA, óbvio, corrigir a sintaxe E verificar parêntesis, operadores e etc, okdoc ???

Abraços,

Chiappa

[Autor]

Posts