Trazer o penúltimo valor lançado,m de uma

[MVlisboa 0]

Preciso por no lugar de " VALORULTENT ", o PCOMPRA do penúltimo pedido feito, que fica na tabela PCITEM. Como poderei fazer isso?

(

SELECT NUMNOTA as NF,

CODPROD as COD,

DESCRICAO as DESCRICAO_PRODUTO,

CAST((QTUNITCX) as INT) as UND,

CAST((PERCIPI) as NUMBER(18, 2)) as PERC_IPI,

CAST((VLIPI * QTUNITCX) as NUMBER(18, 2)) as VL_IPI,

CAST((PERCDESC) as NUMBER(18, 2)) as PERC_DESC,

CAST((VLDESCONTO * QTUNITCX) as NUMBER(18, 2)) as VL_DESC,

CAST((PTABELA * QTUNITCX) as NUMBER(18, 2)) as P_CX,

CAST((VALORULTENT * QTUNITCX) as NUMBER(18, 2)) as P_ANT_CX,

CAST((PTABELA - VALORULTENT) / (PTABELA) * 100 as NUMBER(18, 2)) as PERC_DIF,

CAST((PTABELA - VALORULTENT) * QTUNITCX as NUMBER(18, 2)) as DIF_PRECO

FROM PCMOV

WHERE NUMNOTA = 11405

AND (PUNIT - VALORULTENT> 0.01 OR VALORULTENT - PUNIT > 0.01)

)

[Motta 645]

Como as tabelas se relacionam ?

 

Em tese um subselect pode resolver o problema.

[MVlisboa 0]

Ambas tabelas tem o campo CODPROD e NUMPED, mas como tem que ser o valor referente ao penúltimo pedido, pois preciso fazer o comparativo de preço entre o preço atual e o anterior.

[Motta 645]

Qual seu grau de experiência em Oracle ?

 

Vejo duas soluções a priori

 

Subselect

 

Function

[MVlisboa 0]

Comecei estudar há 4 meses.

[Motta 645]

Tente fazer por subquery.

[MVlisboa 0]

Então... eu sei que dá pra puxar usando uma subquery, mas não estou conseguindo puxar o penúltimo valor real.

Tentei fazer da forma abaixo, mas me parece que ele está somando os valores da notas anteriores.

SELECT PTABELA

  FROM PCMOV 

 WHERE NUMNOTA < (SELECT MAX(NUMNOTA)

                  FROM PCMOV 

                  WHERE CODPROD = 1814 

                  AND CODOPER = 'E' 

                  AND DTCANCEL IS NULL)

AND ROWNUM <=1

ORDER BY NUMNOTA DESC

[Motta 645]

VEJA SE ISTO FUNCIONA

SELECT P1.CODPROD,MAX(NUMNOTA) PENUL_NOTA
FROM PCMOV P1
WHERE CODPROD = 1814 
AND CODOPER = 'E' 
AND DTCANCEL IS NULL
AND NUMNOTA < (SELECT MAX(P2.NUMNOTA)
                 FROM PCMOV P2
                 WHERE P2.CODPROD = P1.CODPROD 
                 AND P2.ODOPER = P1.ODOPER 
                 AND P2.DTCANCEL IS NULL) 
GROUP BY P1.CODPROD

[MVlisboa 0]

 

VEJA SE ISTO FUNCIONA

SELECT P1.CODPROD,MAX(NUMNOTA) PENUL_NOTA
FROM PCMOV P1
WHERE CODPROD = 1814 
AND CODOPER = 'E' 
AND DTCANCEL IS NULL
AND NUMNOTA < (SELECT MAX(P2.NUMNOTA)
                 FROM PCMOV P2
                 WHERE P2.CODPROD = P1.CODPROD 
                 AND P2.ODOPER = P1.ODOPER 
                 AND P2.DTCANCEL IS NULL) 
GROUP BY P1.CODPROD

(SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1)

SELECT A.NUMNOTA AS NF,

A.CODPROD AS COD,

A.DESCRICAO AS DESCRICAO_PRODUTO,

NVL(CAST((A.QTUNITCX) AS INT),0) AS UND,

NVL(CAST((A.PERCIPI) AS NUMBER(18, 2)),0) AS PERC_IPI,

NVL(CAST((A.VLIPI * QTUNITCX) AS NUMBER(18, 2)),0) AS VL_IPI,

NVL(CAST((A.PERCDESC) AS NUMBER(18, 2)),0) AS PERC_DESC,

NVL(CAST((A.VLDESCONTO * A.QTUNITCX) AS NUMBER(18, 2)),0) AS VL_DESC,

NVL(CAST((A.PTABELA * A.QTUNITCX) AS NUMBER(18, 2)),0)AS P_CX,

-- DIFERENÇA DE PREÇO--

NVL(CAST(((SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1) * A.QTUNITCX) AS NUMBER(18, 2)),0) AS P_ANT_CX,

--PERCENTUAL DA DIFERENÇA--

NVL(CAST((A.PTABELA - (SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1)) / (A.PTABELA) * 100 AS NUMBER(18, 2)),0) AS PERC_DIF,

-- DIFERENÇA DE PREÇO--

NVL(CAST((A.PTABELA - (SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1)) * A.QTUNITCX AS NUMBER(18, 2)),0) AS DIF_PRECO

FROM PCMOV A

WHERE A.NUMNOTA = 11405

--PUNIT - PTABELA NF ANTERIOR >0.01 OR PTABELA NF ANTERIOR - PUNIT > 0.01--

AND (A.PUNIT - (SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1) > 0.01 OR (SELECT B.PTABELA

FROM PCMOV B

WHERE B.CODPROD = A.CODPROD

AND B.CODOPER = 'E'

AND B.DTCANCEL IS NULL

AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)

FROM PCMOV C

WHERE C.CODPROD = B.CODPROD

AND C.CODOPER = B.CODOPER

AND C.DTCANCEL IS NULL)

AND ROWNUM <=1) - A.PUNIT > 0.01)

AND A.VALORULTENTANT > 0.01 --USEI "VALORULTENTANT > 0.01" CASO A NF TENHA ALGUM PRODUTO NOVO--

ORDER BY A.PTABELA DESC

SELECT A.NUMNOTA AS NF,
       A.CODPROD AS COD,
       A.DESCRICAO AS DESCRICAO_PRODUTO,
       NVL(CAST((A.QTUNITCX) AS INT),0) AS UND,
       NVL(CAST((A.PERCIPI) AS NUMBER(18, 2)),0) AS PERC_IPI,
       NVL(CAST((A.VLIPI * QTUNITCX) AS NUMBER(18, 2)),0) AS VL_IPI,
       NVL(CAST((A.PERCDESC) AS NUMBER(18, 2)),0) AS PERC_DESC,
       NVL(CAST((A.VLDESCONTO * A.QTUNITCX) AS NUMBER(18, 2)),0) AS VL_DESC,
       NVL(CAST((A.PTABELA * A.QTUNITCX) AS NUMBER(18, 2)),0)AS P_CX,
 
 -- DIFERENÇA DE PREÇO--      
       NVL(CAST(((SELECT B.PTABELA
               FROM PCMOV B 
              WHERE B.CODPROD = A.CODPROD
              AND B.CODOPER = 'E'
              AND B.DTCANCEL IS NULL
              AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)
                                 FROM PCMOV C 
                                WHERE C.CODPROD = B.CODPROD 
                                AND C.CODOPER = B.CODOPER
                                AND C.DTCANCEL IS NULL)
              AND ROWNUM <=1) * A.QTUNITCX) AS NUMBER(18, 2)),0) AS P_ANT_CX,
              
--PERCENTUAL DA DIFERENÇA--              
       NVL(CAST((A.PTABELA - (SELECT B.PTABELA
  FROM PCMOV B 
 WHERE B.CODPROD = A.CODPROD
 AND B.CODOPER = 'E'
 AND B.DTCANCEL IS NULL
 AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)
                    FROM PCMOV C 
                   WHERE C.CODPROD = B.CODPROD 
                   AND C.CODOPER = B.CODOPER
                   AND C.DTCANCEL IS NULL)
 AND ROWNUM <=1)) / (A.PTABELA) * 100 AS NUMBER(18, 2)),0) AS PERC_DIF,

-- DIFERENÇA DE PREÇO--       
       NVL(CAST((A.PTABELA - (SELECT B.PTABELA
  FROM PCMOV B 
 WHERE B.CODPROD = A.CODPROD
 AND B.CODOPER = 'E'
 AND B.DTCANCEL IS NULL
 AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)
                    FROM PCMOV C 
                   WHERE C.CODPROD = B.CODPROD 
                   AND C.CODOPER = B.CODOPER
                   AND C.DTCANCEL IS NULL)
 AND ROWNUM <=1)) * A.QTUNITCX AS NUMBER(18, 2)),0) AS DIF_PRECO
            
  FROM PCMOV A
 WHERE A.NUMNOTA = 11405
 
 --PUNIT - PTABELA NF ANTERIOR >0.01 OR PTABELA NF ANTERIOR - PUNIT > 0.01--
   AND (A.PUNIT - (SELECT B.PTABELA
  FROM PCMOV B 
 WHERE B.CODPROD = A.CODPROD
 AND B.CODOPER = 'E'
 AND B.DTCANCEL IS NULL
 AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)
                    FROM PCMOV C 
                   WHERE C.CODPROD = B.CODPROD 
                   AND C.CODOPER = B.CODOPER
                   AND C.DTCANCEL IS NULL)
 AND ROWNUM <=1) > 0.01 OR (SELECT B.PTABELA
  FROM PCMOV B 
 WHERE B.CODPROD = A.CODPROD
 AND B.CODOPER = 'E'
 AND B.DTCANCEL IS NULL
 AND B.NUMNOTA < (SELECT MAX(C.NUMNOTA)
                    FROM PCMOV C 
                   WHERE C.CODPROD = B.CODPROD 
                   AND C.CODOPER = B.CODOPER
                   AND C.DTCANCEL IS NULL)
 AND ROWNUM <=1) - A.PUNIT > 0.01)

   AND A.VALORULTENTANT > 0.01 --USEI "VALORULTENTANT > 0.01" CASO A NF TENHA ALGUM PRODUTO NOVO--
 ORDER BY A.PTABELA DESC

[Motta 645]

Não entendi mas cuidado com order by e rownum , primeiro o Oracle monta o resulta set depois ordena , os rownums podem vir embaralhados.