Error of consultation per month

Asked

Viewed 324 times

1

I need to let the user choose the month to calculate the ESTDIA.

In red and bold line.

When I leave the :MES and executes the error and leave already set example V.OUTUBRO the SELECT wheel error-free.

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause:
*Action:

SELECT
  V.CODFORNEC,
  V.FORNECEDOR,
  ROUND(V.JANEIRO, 2) JANEIRO,
  ROUND(V.FEVEREIRO, 2) FEVEREIRO,
  ROUND(V.MARCO, 2) MARCO,
  ROUND(V.ABRIL, 2) ABRIL,
  ROUND(V.MAIO, 2) MAIO,
  ROUND(V.JUNHO, 2) JUNHO,
  ROUND(V.JULHO, 2) JULHO,
  ROUND(V.AGOSTO, 2) AGOSTO,
  ROUND(V.SETEMBRO, 2) SETEMBRO,
  ROUND(V.OUTUBRO, 2) OUTUBRO,
  ROUND(V.NOVEMBRO, 2) NOVEMBRO,
  ROUND(V.DEZEMBRO, 2) DEZEMBRO,
  ROUND(V.VL_TOTAL1, 2) VLTOTAL1,
  ROUND(V.VL_TOTAL2, 2) VLTOTAL2,
  ROUND(VL_ESTOQUE.VL_ESTOQUE, 2) VLESTOQUE,

-- Assim roda sem erros: ROUND( (VL_ESTOQUE.VL_ESTOQUE)/DECODE((V.OUTUBRO), 0, 1,(V.OUTUBRO)) * 30,2) ESTDIAS,

-- Deve ficar assim: ROUND( (VL_ESTOQUE.VL_ESTOQUE)/DECODE((:MES), 0, 1,(:MES)) * 30,2) ESTDIAS

FROM
(SELECT
  P1.CODFORNEC,
  P1.FORNECEDOR,
  P1.JANEIRO,
  P1.FEVEREIRO,
  P1.MARCO,
  P1.ABRIL,
  P1.MAIO,
  P1.JUNHO,
  P1.JULHO,
  P1.AGOSTO,
  P1.SETEMBRO,
  P1.OUTUBRO,
  P1.NOVEMBRO,
  P1.DEZEMBRO,
  P1.VL_TOTAL1,
  P2.VL_TOTAL2
FROM
(SELECT
      PCMOV.CODFORNEC,
      PCFORNEC.FORNECEDOR,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '01', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JANEIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '02', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS FEVEREIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '03', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MARCO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '04', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS ABRIL,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '05', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MAIO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '06', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JUNHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '07', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JULHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '08', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS AGOSTO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '09', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS SETEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '10', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS OUTUBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '11', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS NOVEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '12', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS DEZEMBRO,
      TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) AS VL_TOTAL1
FROM
    PCFORNEC PCFORNEC,
    PCMOV PCMOV
WHERE PCFORNEC.CODFORNEC = PCMOV.CODFORNEC
 AND PCMOV.CODFILIAL IN (3)
 AND PCMOV.DTMOV BETWEEN TO_DATE('01/10/2013', 'DD/MM/YYYY') AND TO_DATE('31/10/2013', 'DD/MM/YYYY')
 AND PCMOV.DTCANCEL IS NULL
 AND PCMOV.CODOPER IN ('SB', 'S')
GROUP BY
 PCMOV.CODFORNEC,
 PCFORNEC.FORNECEDOR
 ORDER BY
 TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) DESC)
 P1 ,
 (SELECT
      PCMOV.CODFORNEC,
      PCFORNEC.FORNECEDOR,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '01', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JANEIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '02', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS FEVEREIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '03', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MARCO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '04', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS ABRIL,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '05', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MAIO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '06', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JUNHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '07', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JULHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '08', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS AGOSTO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '09', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS SETEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '10', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS OUTUBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '11', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS NOVEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '12', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS DEZEMBRO,
      TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) AS VL_TOTAL2
FROM
    PCFORNEC PCFORNEC,
    PCMOV PCMOV
WHERE PCFORNEC.CODFORNEC = PCMOV.CODFORNEC
 AND PCMOV.CODFILIAL IN (3)
 AND PCMOV.DTMOV BETWEEN TO_DATE('01/10/2014', 'DD/MM/YYYY') AND TO_DATE('31/10/2014', 'DD/MM/YYYY')
 AND PCMOV.DTCANCEL IS NULL
 AND PCMOV.CODOPER IN ('SB', 'S')
GROUP BY
 PCMOV.CODFORNEC,
 PCFORNEC.FORNECEDOR
 ORDER BY
 TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) DESC) P2
WHERE P1.CODFORNEC = P2.CODFORNEC )V,
(SELECT
    PCPRODUT.CODFORNEC,
    ROUND(SUM(NVL(PCEST.QTESTGER, 0) * NVL(PCTABPR.PVENDA, 0)), 2) AS VL_ESTOQUE
FROM PCEST PCEST
INNER JOIN PCTABPR PCTABPR ON PCEST.CODPROD = PCTABPR.CODPROD
INNER JOIN PCPRODUT PCPRODUT ON PCPRODUT.CODPROD = PCEST.CODPROD
WHERE PCTABPR.NUMREGIAO = 1
AND PCPRODUT.DTEXCLUSAO IS NULL
GROUP BY PCPRODUT.CODFORNEC)
VL_ESTOQUE
WHERE V.CODFORNEC = VL_ESTOQUE.CODFORNEC;
sql oracle

  • It’s actually a formula, no

1 answer

0

It’s actually a formula, isn’t it? 2/L of the month stock ? Try 

SELECT
      V.CODFORNEC,
      V.FORNECEDOR,
      ROUND(V.JANEIRO, 2) JANEIRO,
      ROUND(V.FEVEREIRO, 2) FEVEREIRO,
      ROUND(V.MARCO, 2) MARCO,
      ROUND(V.ABRIL, 2) ABRIL,
      ROUND(V.MAIO, 2) MAIO,
      ROUND(V.JUNHO, 2) JUNHO,
      ROUND(V.JULHO, 2) JULHO,
      ROUND(V.AGOSTO, 2) AGOSTO,
      ROUND(V.SETEMBRO, 2) SETEMBRO,
      ROUND(V.OUTUBRO, 2) OUTUBRO,
      ROUND(V.NOVEMBRO, 2) NOVEMBRO,
      ROUND(V.DEZEMBRO, 2) DEZEMBRO,
      ROUND(V.VL_TOTAL1, 2) VLTOTAL1,
      ROUND(V.VL_TOTAL2, 2) VLTOTAL2,
      ROUND(VL_ESTOQUE.VL_ESTOQUE, 2) VLESTOQUE,


    -- TENTE 
    ROUND( (VL_ESTOQUE.VL_ESTOQUE)/DECODE((V.MESPARAM, 0, 1,V.MESPARAM) * 30,2) ESTDIAS

    FROM
    (SELECT
      P1.CODFORNEC,
      P1.FORNECEDOR,
      P1.JANEIRO,
      P1.FEVEREIRO,
      P1.MARCO,
      P1.ABRIL,
      P1.MAIO,
      P1.JUNHO,
      P1.JULHO,
      P1.AGOSTO,
      P1.SETEMBRO,
      P1.OUTUBRO,
      P1.NOVEMBRO,
      P1.DEZEMBRO,
      P1.VL_TOTAL1,
      P2.VL_TOTAL2
    FROM
(SELECT
      PCMOV.CODFORNEC,
      PCFORNEC.FORNECEDOR,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '01', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JANEIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '02', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS FEVEREIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '03', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MARCO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '04', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS ABRIL,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '05', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MAIO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '06', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JUNHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '07', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JULHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '08', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS AGOSTO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '09', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS SETEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '10', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS OUTUBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '11', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS NOVEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '12', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS DEZEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), MES , (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MESPARAM,
      TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) AS VL_TOTAL1
FROM
    PCFORNEC PCFORNEC,
    PCMOV PCMOV
WHERE PCFORNEC.CODFORNEC = PCMOV.CODFORNEC
 AND PCMOV.CODFILIAL IN (3)
 AND PCMOV.DTMOV BETWEEN TO_DATE('01/10/2013', 'DD/MM/YYYY') AND TO_DATE('31/10/2013', 'DD/MM/YYYY')
 AND PCMOV.DTCANCEL IS NULL
 AND PCMOV.CODOPER IN ('SB', 'S')
GROUP BY
 PCMOV.CODFORNEC,
 PCFORNEC.FORNECEDOR
 ORDER BY
 TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) DESC)
 P1 ,
 (SELECT
      PCMOV.CODFORNEC,
      PCFORNEC.FORNECEDOR,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '01', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JANEIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '02', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS FEVEREIRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '03', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MARCO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '04', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS ABRIL,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '05', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MAIO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '06', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JUNHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '07', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS JULHO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '08', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS AGOSTO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '09', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS SETEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '10', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS OUTUBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '11', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS NOVEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), '12', (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS DEZEMBRO,
      TRUNC(SUM(DECODE(TO_CHAR(PCMOV.DTMOV, 'MM'), MES , (NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 0)), 3) AS MESPARAM,
      TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) AS VL_TOTAL2
FROM
    PCFORNEC PCFORNEC,
    PCMOV PCMOV
WHERE PCFORNEC.CODFORNEC = PCMOV.CODFORNEC
 AND PCMOV.CODFILIAL IN (3)
 AND PCMOV.DTMOV BETWEEN TO_DATE('01/10/2014', 'DD/MM/YYYY') AND TO_DATE('31/10/2014', 'DD/MM/YYYY')
 AND PCMOV.DTCANCEL IS NULL
 AND PCMOV.CODOPER IN ('SB', 'S')
GROUP BY
 PCMOV.CODFORNEC,
 PCFORNEC.FORNECEDOR
 ORDER BY
 TRUNC(NVL(TRUNC(SUM(NVL(PCMOV.QT, 0) * NVL(PCMOV.PUNIT, 0)), 3), 0), 3) DESC) P2
WHERE P1.CODFORNEC = P2.CODFORNEC )V,
(SELECT
    PCPRODUT.CODFORNEC,
    ROUND(SUM(NVL(PCEST.QTESTGER, 0) * NVL(PCTABPR.PVENDA, 0)), 2) AS VL_ESTOQUE
FROM PCEST PCEST
INNER JOIN PCTABPR PCTABPR ON PCEST.CODPROD = PCTABPR.CODPROD
INNER JOIN PCPRODUT PCPRODUT ON PCPRODUT.CODPROD = PCEST.CODPROD
WHERE PCTABPR.NUMREGIAO = 1
AND PCPRODUT.DTEXCLUSAO IS NULL
GROUP BY PCPRODUT.CODFORNEC)
VL_ESTOQUE
WHERE V.CODFORNEC = VL_ESTOQUE.CODFORNEC;

  • Actually I will have to adjust select by doing a Decode, where when inserting number 1 into the filter for example select will handle the V.OCTOBER variable or another month informed.

  • Wouldn’t be the MESPARAM of my query !? I don’t know if I understood the problem.

  • No, as I checked what happens and that I am inserting inside the filter :VMES of routine 800 a database variable, this cannot because the filter inserts the quotes as I inserted in routine 860 it is necessary to adjust the select and the filter in routine 800 doing a Decode, where when inserting in the filter the number 1 for example the select will handle searching the variable V.JANEIRO, the number 2 , will be V.FEB and so on because all text inserted in the filter of routine 800 is treated as being text and not variable or is always the routine will insert quotes.

  • I’m sorry, but I still don’t understand.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.