0

I have an Oracle db and the following tables:

raportor:
id      id_pct_lucru
58382   327342

ses_rap
id  an      status
1   2012    I
21  2013    I
41  2014    D

ch_trat
id      fk_id_raportor  FK_ID_SR
20128   58382           41

taed
cod_deseu   cant    fk_trat_id
12.01.06    137     20128
12.01.13    50      20128
12.01.06    22      20128

tid
id      den     cod_tip
3557    dep3    HS
3555    dep1    B
3556    dep2    HZC

ticd
fk_inst_trat_id     cod
3557                12.01.06
3556                12.01.13
3555                12.01.06

and the following sql:

SELECT RAP.ID_PCT_LUCRU as id_pl, 
          ses_rap.an_raportare as an,
          taed.COD_DESEU as cod,
          sum(taed.cant) as cant,
          LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP) AS tip_op,
     FROM ch_trat cht
          INNER JOIN raportor rap
             ON cht.fk_id_raportor = rap.id
          INNER JOIN ses_rap
             ON ses_rap.id = CHT.FK_ID_SR
          INNER JOIN taed
             ON taed.FK_TRAT_ID = CHT.ID
          INNER JOIN tid
             ON tid.FK_TRAT_ID = CHT.ID
          INNER JOIN ticd
             ON TICD.FK_INST_TRAT_ID = TID.ID
             and TICD.COD=taed.COD_DESEU
    WHERE SES_RAP.STATUS = 'D'
        group by RAP.ID_PCT_LUCRU,
            SES_RAP.AN_RAPORTARE,
            CHT.ID,
            taed.COD_DESEU
     order by RAP.ID_PCT_LUCRU

and the result is:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    318     B,B,HS,HS
327342  2014    12.01.13    50      HZC

but I want the result:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    159     B,HS
327342  2014    12.01.13    50      HZC

and I don't know how to do it. Please HELP!

1

For your SUM issue, please see this article for a detailed description of Oracle solutions that may be helpful --> https://community.oracle.com/thread/2137764?tstart=0

For the LISTAGG issue with duplicate values and values from other columns, check out this article for some solutions that may work for your need --> https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values

With some quick reading and not being an Oracle SQL guy, this seems as if there are a couple things in these that may help but I cannot easily provide an example query for you to use at the current moment unfortunately with this table data examples you provided.

Hopefully this will be helpful or point you in the right direction for the two issues I see with what results you get and what results you need.

1
  • I fail to achieve the desired results...
    – WDrgn
    Oct 12 '15 at 11:55
0

I did it that way:

SELECT RAP.ID_PCT_LUCRU as id_pl,
          ses_rap.an_raportare as an,
          taed.COD_DESEU as cod,
          (select sum(TAED2.CPREL) from taed taed2
            where TAED2.COD_DESEU=TAED.COD_DESEU and TAED2.FK_TRAT_ID=CHT.ID
          ) as cant, 
          unique_list(LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP)) AS tip_op
     FROM ch_trat cht
          INNER JOIN raportor rap
             ON cht.fk_id_raportor = rap.id
          INNER JOIN ses_rap
             ON ses_rap.id = CHT.FK_ID_SR
          INNER JOIN taed
             ON taed.FK_TRAT_ID = CHT.ID
          inner JOIN tid
             ON tid.FK_TRAT_ID = CHT.ID
          inner JOIN ticd
             ON TICD.FK_INST_TRAT_ID = TID.ID
             and TICD.COD=taed.COD_DESEU
    WHERE SES_RAP.STATUS = 'D' AND taed.COD_DESEU IS NOT NULL
        group by RAP.ID_PCT_LUCRU,
            SES_RAP.AN_RAPORTARE,
            CHT.ID,
            TAED.COD_DESEU
     order by RAP.ID_PCT_LUCRU

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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