I need to make it so that the user chooses the month to calculate ESTDIA.
On the red and bold line.
When I leave the :MES and execute of the error and already leave defined example V.OUTUBRO o SELECT runs without errors.
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;