single-row subquery returns more than one row - how to find the duplicate?

Question

iam not a big ORACLE - SQL Expert, so i hope someone knows a good way to find the "duplicate" record wich is causing the: single-row subquery returns more than one row error.

This my Statement:

    SELECT
    CAST(af.SAP_SID AS VARCHAR2(4000)) APP_ID,
    (SELECT DR_OPTION
       FROM
          DR_OPTIONS 
       WHERE DR_OPTIONS.ID = (
            select dr_option from applications where applications.sap_sid = af.sap_sid)) DR_OPTION
FROM
    APPLICATIONS_FILER_VIEW af

it works on my test system, so iam "sure" there must be an error inside the available data records, but i have no idea how to find those ..

Answer 1

Try with this query:

select applications.sap_sid, count(dr_option) 
from applications 
group by applications.sap_sid 
having count(dr_option) > 1

This should give you the sap_sid of the duplicated rows