I put a query where I bring some information, of which the last column returns positive, negative and zero values.
I do not want to display the rows with negative values, but I'm not sure how to do that.
This is Query
SELECT C.SG_CONCESSIONARIO,
A.NU_SG,
A.NU_CHASSI,
V.DS_NOME_MODELO,
V.CD_MODELO_GARANTIA,
B.CD_PECA_APLICADA,
P.DS_PECA_IDIOMA1,
P.QT_VENDA_MINIMA,
(FN_RETORNA_QTD(B.CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO)) AS QTD_CATALOGO,
B.PR_PECA_ORIGINAL AS VL_UNITARIO,
B.QT_PECA as QTD_SOLICITADO,
(B.PR_PECA_ORIGINAL *
(FN_RETORNA_QTD(B.CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO))) AS PRECO_CAT,
((B.PR_PECA_ORIGINAL + B.VL_ICMS) * B.QT_PECA) AS PRECO_GAR,
((B.PR_PECA_ORIGINAL + B.VL_ICMS) * B.QT_PECA) -
(B.PR_PECA_ORIGINAL *
(FN_RETORNA_QTD(B.CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO))) AS PRECO_DIF
FROM GARANTIA A
LEFT JOIN GARANTIA_PC B
on A.NU_ORDEM_SERVICO = B.NU_ORDEM_SERVICO
AND A.NU_SEQ_ORDEM_SERVICO = B.NU_SEQ_ORDEM_SERVICO
AND A.CD_CONCESSIONARIO = B.CD_CONCESSIONARIO
LEFT JOIN VEICULO V
ON A.NU_CHASSI = V.NU_CHASSI
LEFT JOIN MODEL_MASTER O
ON v.cd_catalogo = O.CD_PRODUCAO
LEFT JOIN PECA P
ON B.CD_PECA_APLICADA = P.CD_PECA
LEFT JOIN CAT_H5J021D K
ON O.Illust_Pn = K.Illust_Pn
LEFT JOIN CAT_H5J024D M
ON K.BOOK_NO = M.BOOK_NO
AND B.CD_PECA_APLICADA = M.PART_NO,
CONCESSIONARIO C
WHERE A.DT_EXCLUSAO IS NULL
AND B.DT_EXCLUSAO IS NULL
AND C.DT_EXCLUSAO IS NULL
AND A.CD_CONCESSIONARIO = C.ID_CONCESSIONARIO
AND A.CD_TIPO_GARANTIA != 'C'
AND A.DT_INCLUSAO BETWEEN TO_DATE('01/07/2015', 'dd/MM/yyyy') AND
TO_DATE('02/07/2015', 'dd/MM/yyyy')
order by NU_CHASSI, CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO
Function FN_RETORNA_QTD
create or replace function FN_RETORNA_QTD(peca in varchar2,
catalogo in varchar2,
page_no in varchar2)
return varchar2 is
v_qtd number(2);
begin
select qtd
into v_qtd
from (select M.QTY_1 as qtd
from CAT_H5J024D M
WHERE PART_NO = peca
and m.book_no = catalogo
and M.QTY_1 is not null
and M.QTY_1 != 'AR'
and M.PAGE_NO = page_no
union
select M.QTY_2 as qtd
from CAT_H5J024D M
WHERE PART_NO = peca
and m.book_no = catalogo
and M.QTY_2 is not null
and M.QTY_2 != 'AR'
and M.PAGE_NO = page_no
union
select M.QTY_3 as qtd
from CAT_H5J024D M
WHERE PART_NO = peca
and m.book_no = catalogo
and M.QTY_3 is not null
and M.QTY_3 != 'AR'
and M.PAGE_NO = page_no
union
select M.QTY_4 as qtd
from CAT_H5J024D M
WHERE PART_NO = peca
and m.book_no = catalogo
and M.QTY_4 is not null
and M.QTY_4 != 'AR'
and M.PAGE_NO = page_no
union
select M.QTY_5 as qtd
from CAT_H5J024D M
WHERE PART_NO = peca
and m.book_no = catalogo
and M.QTY_5 is not null
and M.QTY_5 != 'AR'
and M.PAGE_NO = page_no
)
where rownum = 1;
return(v_qtd);
end FN_RETORNA_QTD;