single-row subquery returns more than one row - when query should return 1 row

Question

I am expecting a single result object from th below query with the following input.

But I am getting this error:

ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"

Data is:

    id  ssn     Name    Branch  City    DepntSsn  
   ----------------------------------------------
    01  100     AAA     IT      CA      101         
    02  101     BBB     RA      VA      104 
    03  101     BBB     RA      VA      104     

Oracle query:

SELECT
    id,
    ssn,
    name,
    branch,
    city,
    depntssn,
    CASE WHEN branch == 'IT' then (select id from case where ssn = DepntSsn) else null end DepntId 
FROM
    case
WHERE
    ssn = 100   
     )
WHERE
    ROWNUM = 1
     ;

Output should be :

id  ssn     Name    Branch  City    DepntSsn  DepntId
-----------------------------------------------------
01  100     AAA     IT      CA      101         02

Answer 1

The error seems pretty obvious. This subquery:

(select id from case where ssn = DepntSsn)

returns more than one row. Well, the reason might have to do with the fact that this is the same table, so this is really doing:

(select c2.id from case c2 where c2.ssn = c2.DepntSsn)

which is certainly not what you intend. I suspect you want something like this:

select . . .,
       (select c2.id from case c2 where c2.ssn = c.DepntSsn)
from case c
. . .

When your query has more than one table reference, you should always qualify all column references. This is particularly important for correlated subqueries!

Also, case is a lousy name for a table, because it is a SQL keyword.