Erroneous ORA-01427: single-row subquery returns more than one row

Question

I'm getting the error [ORA-01427: single-row subquery returns more than one row] when I execute a query. I have a query structured like so:

SELECT LV.PRICE,
 (SELECT C.MODEL_NAME FROM CARS C WHERE C.MODEL_ID = LV.MODEL_ID) as MODEL_NAME
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

It's breaking on the nested select. I know the logic both in the view and in this query is correct, and that there's no chance of the nested select returning more than one row. The CARS table's MODEL_ID is a unique field. If I execute the query without the nested select it doesn't return this error.

The LEDGER_VIEW is a view built on top of another view. Is it possible that these stacked views are buggy in Oracle 10g? I don't know how else to debug this problem.

I am aware I could change this particular query to a join rather than a nested select, but I'd like to know why this is happening because I use nested queries in other places where it is not so easily modifiable.

EDIT: Here's the really strange thing. The LEDGER_VIEW is, as I said, built on top of another view. As a test, I copied the nested view's SQL directly into the SQL of the SQL of LEDGER_VIEW, in place of the nested view, and it returned with no errors (as expected). This seems to confirm to me that there is some buggy behavior either with nested views or with the combination of nested views + database links.

Answer 1

Your subquery is returning multiple rows. Use the query below to find out which MODELID values in the Car table are duplicated:

select MODELID as CarsModelID, count(*) as Count
from cars 
where MODELID in (
    select MODEL_ID
    from LEDGER_VIEW  
    WHERE LV.PRICE < 500 
)
group by MODELID
having count(*) > 1