Grouping of lines Group By

0

I have the following query:

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, however I have a scenario where an item has 2 tax lines, and these 2 lines have some fields that make the grouping different, so it is repeating values, as below :

Notethatwhenthehighlightedlineisrepeated,thecolumn"VLR_BRUTO" is printed again with the same value, how could I display this column zeroed? Is there a way to do this in a query?

    
asked by anonymous 04.01.2017 / 18:48

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
    
04.01.2017 / 18:55