OracleDB: COALESCE and ORA-01427: single-row subquery returns more than one row

Question

I use COALESCEfunction to avoid ORA-01427 and pick up first non-null value.

COALESCE((
        SELECT c.SCounts
        FROM counts c
        WHERE c.ID = 10000
        ), 0)

When I comment this code everything works well.

Answer 1

I suppose the above COALESCE is embedded into a SELECT something like this:

SELECT
COALESCE((
        SELECT c.SCounts
        FROM counts c
        WHERE c.ID = 10000
        ), 0)
FROM counts

If this is true, then the problem could be that this select will return as many rows as table COUNTS has.