0

I have a poorly-designed table in an Oracle database like this:

sr:
id  an_rap
21  2015

rap:
id      cf
28185   123-123

ch:
id      fk_id_rap   FK_ID_SR
12348   28185       21

dma:
ch_id   FK_AMB_ST
12348   18059

cant:
id      FK_ID_MUN   CANT_VAL    COD_VALORIFICARE
18059   12348       18.56       R12     

col:
FK_ID_COL_DMA   CANT_VAL    COD_VALORIFICARE
18059           1134        R10
18059           1234        R3

if I made an:

select
    SR.AN_RAP as AN, RAP.CF,
    max(CANT.CANT_VAL) as CANT_VAL,
    max(CANT.COD_VALORIFICARE) as COD_VALORIFICARE,
    max(COL.CANT_VAL) as kk0,
    max(COL.COD_VALORIFICARE) as kk1
from dma
    inner join ch on ch.id=DMA.CH_ID
    inner join cant on CANT.FK_ID_MUN=ch.id
    full outer join col on cant.id=COL.FK_ID_COL_DMA
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
where RAP.CF='123-123'
group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
    CANT.COD_VALORIFICARE,COL.COD_VALORIFICARE
order by sr.an_rap desc,rap.cf,cant.id

the result is:

an      cf          CANT_VAL    COD_VALORIFICARE    kk0     kk1
2015    123-123     18.56       R12                 1134    R10
2015    123-123     18.56       R12                 1234    R3

how can I make something like this - without changing the structure of the database???

an      cf          CANT_VAL    COD_VALORIFICARE
2015    123-123     18.56       R12
2015    123-123     1134        R10
2015    123-123     1234        R3

Thanks!

1 Answer 1

0

I solved it that way:

  select
        SR.AN_RAP as AN, RAP.CF,
        max(col_cant.CANT_VAL) as CANT_VAL,
        max(col_cant.COD_VALORIFICARE) as COD_VALORIFICARE
    from dma
        inner join ch on ch.id=DMA.CH_ID
    inner join
            (select CANT.ID,CANT.FK_ID_MUN,CANT.CANT_VAL,CANT.COD_VALORIFICARE from cant
            union
            select  COL.FK_ID_COL_DMA,CANT2.FK_ID_MUN,COL.CANT_VAL, COL.COD_VALORIFICARE from col 
                inner join cant2 on cant2.id=COL.FK_ID_COL_DMA
            ) col_cant 
       on col_cant.FK_ID_MUN=ch.id
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
    where RAP.CF='123-123'
    group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
            col_cant.COD_VALORIFICARE
    order by sr.an_rap desc,rap.cf,col_cant.id

Not the answer you're looking for? Browse other questions tagged or ask your own question.