CODEWITHSUNDEEP
oracle-databasecoalesce
Rudziankoŭ
Rudziankoŭ

Reputation: 11251

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

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.

Upvotes: 0

Views: 478

Answers (1)

Galcoholic
Galcoholic

Reputation: 518

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.

Upvotes: 1

Related Questions