0

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!

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

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.