Grouping of lines Group By

Asked

Viewed 80 times

0

I have the following appointment:

SELECT distinct CAPA.COD_HOLDING,
    CAPA.COD_MATRIZ,
    CAPA.COD_FILIAL,
    CAPA.ID_NF_ENTRADA,
    CAPA.NUM_NF,
    CAPA.DT_ENTRADA,
    CAPA.DT_EMISSAO,
    CAPA.COD_CLIFOR,
    CAPA.COD_UF,
    CAPA.CGC_CPF,
    CAPA.COD_CFOP_LEGAL,
    CAPA.CHAVE_NF_E,
    ITEM.ID_ITEM,
    ITEM.COD_CLASSIF_FISCAL,
    ITEM.COD_IVA,
    ITEM.DESCRICAO_NOTA,
    ITEM.COD_PRODUTO,
    (ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS) AS VLR_BRUTO,
    IMP.COD_IMPOSTO,
    IMP.COD_TP_LANC_IMP,
    DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0) AS BASE_CALCULO,
    IMP.ALIQUOTA,
    DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0) AS VLR_IMPOSTO,
    OBS.COD_OBSERVACAO,
    DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0) AS ISENTAS,
    DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0) AS OUTRAS

FROM LF_NF_ENTRADA                CAPA,
    LF_NF_ENTRADA_ITEM           ITEM,
    LF_NF_ENTRADA_IMPOSTO        IMP,
    LF_NF_ENTRADA_OBSERVACOES    OBS

WHERE CAPA.COD_HOLDING          = ITEM.COD_HOLDING
   AND CAPA.COD_MATRIZ          = ITEM.COD_MATRIZ
   AND CAPA.COD_FILIAL          = ITEM.COD_FILIAL
   AND CAPA.ID_NF_ENTRADA       = ITEM.ID_NF_ENTRADA
   AND ITEM.COD_HOLDING         = IMP.COD_HOLDING
   AND ITEM.COD_MATRIZ          = IMP.COD_MATRIZ
   AND ITEM.COD_FILIAL          = IMP.COD_FILIAL
   AND ITEM.ID_NF_ENTRADA       = IMP.ID_NF_ENTRADA
   AND ITEM.ID_ITEM             = IMP.ID_ITEM
   AND CAPA.COD_HOLDING         = OBS.COD_HOLDING
   AND CAPA.COD_MATRIZ          = OBS.COD_MATRIZ
   AND CAPA.COD_FILIAL          = OBS.COD_FILIAL
   AND CAPA.ID_NF_ENTRADA       = OBS.ID_NF_ENTRADA
   AND CAPA.COD_HOLDING         = 'HOL00'
   AND CAPA.COD_MATRIZ          = 'I003'
   AND CAPA.COD_FILIAL          = '0003'
   AND CAPA.DT_ENTRADA          >= TO_DATE('01/05/2016', 'DD/MM/YYYY')
   AND CAPA.DT_ENTRADA          <= TO_DATE('31/05/2016', 'DD/MM/YYYY')
   AND IMP.COD_IMPOSTO          IN ('01')
   AND CAPA.COD_STATUS          = 01
   AND CAPA.COD_CFOP_LEGAL      = '2551'

GROUP BY CAPA.COD_HOLDING,
       CAPA.COD_MATRIZ,
       CAPA.COD_FILIAL,
       CAPA.ID_NF_ENTRADA,
       CAPA.NUM_NF,
       CAPA.DT_ENTRADA,
       CAPA.DT_EMISSAO,
       CAPA.COD_CLIFOR,
       CAPA.COD_UF,
       CAPA.CGC_CPF,
       CAPA.COD_CFOP_LEGAL,
       CAPA.CHAVE_NF_E,
       ITEM.ID_ITEM,
       ITEM.COD_CLASSIF_FISCAL,
       ITEM.COD_IVA,
       ITEM.DESCRICAO_NOTA,
       ITEM.COD_PRODUTO,
       (ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS),
       IMP.COD_IMPOSTO,
       IMP.COD_TP_LANC_IMP,
       DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0),
       IMP.ALIQUOTA,
       DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0),
       OBS.COD_OBSERVACAO,
       DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0),
       DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0)  

ORDER BY CAPA.COD_HOLDING,
       CAPA.COD_MATRIZ,
       CAPA.COD_FILIAL,
       CAPA.NUM_NF,
       CAPA.DT_ENTRADA,
       IMP.COD_IMPOSTO,
       ITEM.ID_ITEM;

In it I return tax records with the respective tax and value, but I have a scenario where an item has 2 lines of taxes, and these 2 lines have some fields that I do the grouping different, so it is repeating values, as below:

Demonstração do problema

Note that when the highlighted row is repeated, the "VLR_BRUTO" column is printed again with the same value, how could I display this zeroed column? Is there any way to do this in a query ?

  • Every time the value is repeated the BASE_CALCULO is reset, or the COD_TIPO_LANC_IMP is greater than 1?

  • Reginaldo, the line is being duplicated because for this item there are two lines of the tax in question. One of them has the COD_TP_LANC_IMP field equal to 1 and the other with the value 2, so the record is being repeated.

  • If you only selected items whose BASE_CALCULO is greater than zero would be right or you need it to come to duplicate lines including?

  • See date conversion and session format https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119 https://docs.oracle.com/cd/B28359_01/server.111/b286/sql_elements004.htm#SQLRF00210

1 answer

0

You could do something like this:

SELECT * FROM (
SELECT distinct CAPA.COD_HOLDING,
        CAPA.COD_MATRIZ,
        CAPA.COD_FILIAL,
        CAPA.ID_NF_ENTRADA,
        CAPA.NUM_NF,
        CAPA.DT_ENTRADA,
        CAPA.DT_EMISSAO,
        CAPA.COD_CLIFOR,
        CAPA.COD_UF,
        CAPA.CGC_CPF,
        CAPA.COD_CFOP_LEGAL,
        CAPA.CHAVE_NF_E,
        ITEM.ID_ITEM,
        ITEM.COD_CLASSIF_FISCAL,
        ITEM.COD_IVA,
        ITEM.DESCRICAO_NOTA,
        ITEM.COD_PRODUTO,
        (ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS) AS VLR_BRUTO,
        IMP.COD_IMPOSTO,
        IMP.COD_TP_LANC_IMP,
        DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0) AS BASE_CALCULO,
        IMP.ALIQUOTA,
        DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0) AS VLR_IMPOSTO,
        OBS.COD_OBSERVACAO,
        DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0) AS ISENTAS,
        DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0) AS OUTRAS
  FROM LF_NF_ENTRADA                CAPA,
       LF_NF_ENTRADA_ITEM           ITEM,
       LF_NF_ENTRADA_IMPOSTO        IMP,
       LF_NF_ENTRADA_OBSERVACOES    OBS
 WHERE CAPA.COD_HOLDING         = ITEM.COD_HOLDING
   AND CAPA.COD_MATRIZ          = ITEM.COD_MATRIZ
   AND CAPA.COD_FILIAL          = ITEM.COD_FILIAL
   AND CAPA.ID_NF_ENTRADA       = ITEM.ID_NF_ENTRADA

   AND ITEM.COD_HOLDING         = IMP.COD_HOLDING
   AND ITEM.COD_MATRIZ          = IMP.COD_MATRIZ
   AND ITEM.COD_FILIAL          = IMP.COD_FILIAL
   AND ITEM.ID_NF_ENTRADA       = IMP.ID_NF_ENTRADA
   AND ITEM.ID_ITEM             = IMP.ID_ITEM

   AND CAPA.COD_HOLDING         = OBS.COD_HOLDING
   AND CAPA.COD_MATRIZ          = OBS.COD_MATRIZ
   AND CAPA.COD_FILIAL          = OBS.COD_FILIAL
   AND CAPA.ID_NF_ENTRADA       = OBS.ID_NF_ENTRADA

   AND CAPA.COD_HOLDING         = 'HOL00'
   AND CAPA.COD_MATRIZ          = 'I003'
   AND CAPA.COD_FILIAL          = '0003'
   AND CAPA.DT_ENTRADA          >= TO_DATE('01/05/2016', 'DD/MM/YYYY')
   AND CAPA.DT_ENTRADA          <= TO_DATE('31/05/2016', 'DD/MM/YYYY')
   AND IMP.COD_IMPOSTO          IN ('01')
   AND CAPA.COD_STATUS           = 01
   AND CAPA.COD_CFOP_LEGAL                      = '2551'

GROUP BY CAPA.COD_HOLDING,
       CAPA.COD_MATRIZ,
       CAPA.COD_FILIAL,
       CAPA.ID_NF_ENTRADA,
       CAPA.NUM_NF,
       CAPA.DT_ENTRADA,
       CAPA.DT_EMISSAO,
       CAPA.COD_CLIFOR,
       CAPA.COD_UF,
       CAPA.CGC_CPF,
       CAPA.COD_CFOP_LEGAL,
       CAPA.CHAVE_NF_E,
       ITEM.ID_ITEM,
       ITEM.COD_CLASSIF_FISCAL,
       ITEM.COD_IVA,
       ITEM.DESCRICAO_NOTA,
       ITEM.COD_PRODUTO,
       (ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS),
       IMP.COD_IMPOSTO,
       IMP.COD_TP_LANC_IMP,
       DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0),
       IMP.ALIQUOTA,
       DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0),
       OBS.COD_OBSERVACAO,
       DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0),
       DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0)  

ORDER BY CAPA.COD_HOLDING,
       CAPA.COD_MATRIZ,
       CAPA.COD_FILIAL,
       CAPA.NUM_NF,
       CAPA.DT_ENTRADA,
       IMP.COD_IMPOSTO,
       ITEM.ID_ITEM ) WHERE BASE_CALCULO > 0

Browser other questions tagged

You are not signed in. Login or sign up in order to post.