I must not show negative results

Question

I created a query where I bring some information, among this information the last column returns positive, negative and zeroed values.

I don’t want you to display the lines with negative values, but I don’t know how to do this.

This is the 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;

Answer 1

The expression that calculates PRECO_DIF can be simplified to:

B.PR_PECA_ORIGINAL*(B.QT_PECA + B.VL_ICMS - FN_RETORNA_QTD(B.CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO))

This shows that it is not enough to do B.PR_PECA_ORIGINAL >= 0 or FN_RETORNA_QTD >=0, since if the result returned by FN_RETORNA_QTD is greater than the sum between B.QT_PECA + B.VL_ICMS, then the price will be negative.

In this case, therefore, you can include the following expression in your Where clause:

AND (B.QT_PECA + B.VL_ICMS - FN_RETORNA_QTD(B.CD_PECA_APLICADA, M.BOOK_NO, M.PAGE_NO)) >= 0

Another solution would be to create a temporary table with the result of SQL and then make an SQL in this table where PRECO_DIF >= 0.

Another solution is to put this SQL inside another and in the most external SQL WHERE clasp do PRECO_DIF >= 0.