I should not display negative results

2

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;
    
asked by anonymous 31.07.2015 / 22:08

1 answer

1

The expression that calculates the 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 make 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 SQL result and then make an SQL in that table WHERE PRECO_DIF> = 0.

Another solution is to put this SQL inside another and in the WHERE clause of the outermost SQL do PRECO_DIF > = 0.

    
03.08.2015 / 14:29