Tudo na mesma linha

[lyccos]

Bom dia pessoal

Na query abaixo são trazidos 3 impostos para a mesma linha

Selecionar tudo

SELECT RFC.CLASSIFICATION_CODE Código,RFC.DESCRIPTION Descrição, RFC.MIGRATION_CLASSIFICATION_CODE "Código para escrituração", RFCD.TAX_CODE Imposto, RFCD.TAX_PERC Alícota
FROM rec_fiscal_classif_details RFCD, REC_FISCAL_CLASSIFICATIONS RFC
WHERE RFCD.END_DATE IS NULL
AND RFCD.TAX_INACTIVE_DATE IS NULL
AND RFCD.TAX_CODE IN ('PIS','COFINS','IPI')
AND RFC.CLASSIFICATION_ID = RFCD.CLASSIFICATION_ID
AND RFC.CLASSIFICATION_CODE >= '33030000'
order by RFC.CLASSIFICATION_CODE

Fica assim:

33030010 | PERFUMES (EXTRATOS) | 33030010 | IPI | 42
33030010 | PERFUMES (EXTRATOS) | 33030010 | PIS | 0
33030010 | PERFUMES (EXTRATOS) | 33030010 | COFINS | 0

Gostaria que ficassem todos os impostos na mesma linha, conforme abaixo:

33030010 | PERFUMES (EXTRATOS) | 33030010 | IPI | 42 | PIS | 0 | COFINS | 0

Obs: Os impostos podem diminuir por item, por exemplo ter só IPI, podendo ser no máximo os 3 informados.

Obrigado!

Lyccos

[Diego_Mello]

Opa...
Creio que seja algo assim...

Selecionar tudo

SELECT rfc.classification_code código
      ,rfc.description descrição
      ,rfc.migration_classification_code "Código para escrituração"
       --/ IPI
       ,(SELECT rfcd.tax_code
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'IPI'
           AND rfc.classification_id = rfcd.classification_id) imposto_ipi
       ,(SELECT rfcd.tax_perc
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'IPI'
           AND rfc.classification_id = rfcd.classification_id) aliquota_ipi
       --/ COFINS
       ,(SELECT rfcd.tax_code
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'COFINS'
           AND rfc.classification_id = rfcd.classification_id) imposto_cofins
       ,(SELECT rfcd.tax_perc
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'COFINS'
           AND rfc.classification_id = rfcd.classification_id) aliquota_cofins
       --/ PIS
       ,(SELECT rfcd.tax_code
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'PIS'
           AND rfc.classification_id = rfcd.classification_id) imposto_pis
       ,(SELECT rfcd.tax_perc
          FROM rec_fiscal_classif_details rfcd
         WHERE rfcd.end_date IS NULL
           AND rfcd.tax_inactive_date IS NULL
           AND rfcd.tax_code = 'PIS'
           AND rfc.classification_id = rfcd.classification_id) aliquota_pis
  FROM rec_fiscal_classifications rfc
 WHERE rfc.classification_code >= '33030000'
 ORDER BY rfc.classification_code

[Tineks]

Lyccos, provavelmente você já solucionou seu problema, mas vou deixar aqui uma dica, onde você le as tabelas "rec_fiscal_classif_details,rec_fiscal_classifications" uma única vez.

Selecionar tudo

SQL> with rec_fiscal_classifications as
  2  (
  3    select 1 classification_id, 33030000 classification_code, 33030000 migration_classification_code, 'PERFUMES (EXTRATOS)' description from dual
  4  ),
  5  rec_fiscal_classif_details as
  6  (
  7    select 1 classification_id, 'PIS'    tax_code, null end_date, null tax_inactive_date, 0 tax_perc from dual
  8    union
  9    select 1 classification_id, 'COFINS' tax_code, null end_date, null tax_inactive_date, 0 tax_perc from dual
 10    union
 11    select 1 classification_id, 'IPI'    tax_code, null end_date, null tax_inactive_date,42 tax_perc from dual
 12  )
 13  select codigo
 14        ,descricao
 15        ,escrituracao
 16        ,'IPI' IPI
 17        ,sum(decode(num_linha,1,alícota)) vlr_ipi
 18        ,'PIS' PIS
 19        ,sum(decode(num_linha,2,alícota)) vlr_pis
 20        ,'COFINS' COFINS
 21        ,sum(decode(num_linha,3,alícota)) vlr_cofins
 22    from (select rfc.classification_code           codigo
 23                ,rfc.description                   descricao
 24                ,rfc.migration_classification_code escrituracao
 25                ,rfcd.tax_code                     imposto
 26                ,rfcd.tax_perc                     alícota
 27                ,row_number() over (partition by rfc.classification_code order by decode(rfcd.tax_code,'IPI',1,'PIS',2,'COFINS',3)) num_linha
 28            from rec_fiscal_classif_details rfcd
 29                ,rec_fiscal_classifications rfc
 30           where rfcd.end_date              is null
 31             and rfcd.tax_inactive_date     is null
 32             and rfcd.tax_code              in ('PIS', 'COFINS', 'IPI')
 33             and rfc.classification_id       = rfcd.classification_id
 34             and rfc.classification_code    >= '33030000'
 35           order by rfc.classification_code)
 36  group by codigo
 37        ,descricao
 38        ,escrituracao
 39  /

    CODIGO DESCRICAO           ESCRITURACAO IPI    VLR_IPI PIS    VLR_PIS COFINS VLR_COFINS
---------- ------------------- ------------ --- ---------- --- ---------- ------ ----------
  33030000 PERFUMES (EXTRATOS)     33030000 IPI         42 PIS          0 COFINS          0

[]s!!