Joining Oracle tables with 2 strings to be oa a single row (in a distinct SQL filter)

Question

I have an Oracle Database and if I fire that SQL:

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    A3T2.COD_REC, A3T2.COD_VAL
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me:

ID_PL   AN    DENUMIRE  COD_DESEU   TIP_AMB  COD_REC   COD_VAL
353940  2013  DIA       15 01 03    LEMN     R 3    
353940  2013  DIA       15 01 03    LEMN               R 1

but if I make an

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    (select coalesce(A32.COD_REC,'') || coalesce(A32.COD_VAL,'') 
     FROM a3_t2_rows a32
     WHERE A32.TIP_AMB=A3T2.TIP_AMB AND 
        A32.FK_ID_ANEXA = CHT.ID AND 
        A32.COD_DESEU = A3T2.COD_DESEU AND
        A3T2.TIP_AMB = 'LEMN'
     ) as cod_op_lemn
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me an

[Error] ORA-01427: single-row subquery returns more than one row

The question is - how can I join R3 from COD_REC and R1 from COD_VAL so it can be R3 R1 in both rows being a single row with distinct. Thanks!

Answer 1

Simply do a GROUP BY instead of DISTINCT:

SELECT RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    coalesce(MIN(A32.COD_REC),'') || coalesce(MIN(A32.COD_VAL),'') 
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'
GROUP BY RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB