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

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

i'm getting error [ora-01427: single-row subquery returns more 1 row] when execute query. have query structured so:

select lv.price, (select c.model_name cars c c.model_id = lv.model_id) model_name ledger_view lv lv.price < 500

it's breaking on nested select. know logic both in view , in query correct, , there's no chance of nested select returning more 1 row. cars table's model_id unique field. if execute query without nested select doesn't homecoming error.

the ledger_view view built on top of view. possible these stacked views buggy in oracle 10g? don't know how else debug problem.

i aware alter particular query bring together rather nested select, i'd know why happening because utilize nested queries in other places not modifiable.

edit: here's unusual thing. ledger_view is, said, built on top of view. test, copied nested view's sql straight sql of sql of ledger_view, in place of nested view, , returned no errors (as expected). seems confirm me there buggy behavior either nested views or combination of nested views + database links.

i unable recreate via creation of stacked view. (althoug redfilters find culprit)

create table t1 ( t1_id number , txt varchar2( 50 ), constraint t1_pk primary key( t1_id ) ) ; create table t2 ( t2_id number , t1_id number , cost number( 10, 4 ) , constraint t2_pk primary key( t2_id ), constraint t2_fk foreign key( t1_id ) references t1( t1_id ) ); insert t1(t1_id, txt) values(1,'fit'); insert t1(t1_id, txt) values(2,'focus'); insert t1(t1_id, txt) values(3,'golf'); insert t1(t1_id, txt) values(4,'explorer'); insert t1(t1_id, txt) values(5,'corolla'); insert t2(t2_id, t1_id, price) values(1,1,17000); insert t2(t2_id, t1_id, price) values(2,2,16000); insert t2(t2_id, t1_id, price) values(3,3,22000); insert t2(t2_id, t1_id, price) values(4,4,31000); insert t2(t2_id, t1_id, price) values(5,5,17000); create view t1_view select * t1; create view t2_view select * t2; create view t_stacked_view select t1_view.txt , t2_view.price , t1_view.t1_id t1_view left bring together t2_view on t1_view.t1_id = t2_view .t1_id ; --stacked view test select t1_view.txt , (select t_stacked_view.price t_stacked_view t1_view.t1_id = t_stacked_view .t1_id) cost t1_view ; --or improve yet, drop row level query select t1_view.txt , t2_view.price t1_view left bring together t2_view on t1_view.t1_id = t2_view .t1_id ;

but begs question, why doing row level query here? while 10g ought optimize them same, have found easier write queries below, both readability, maintainability, , avoid error having (is always, 3 years downwards road, guaranteed application (both in db , calling app) cannot have status cause error? 1 rouge statement gets in , entire app dies?

select lv.price, c.model_name ledger_view lv left /* or inner */ bring together cars c on c.model_id = lv.model_id lv.price < 500

sql oracle ora-01427