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?