views:

203

answers:

4

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.

+4  A: 

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
RedFilter
This query returns 0 records. My original one returns 5 rows, and if I add LV.MODEL_ID to the returned columns, all 5 rows have MODEL_ID = 27. If I then hardcode C.MODEL_ID = 27 in place of C.MODEL_ID = LV.MODEL_ID it returns the expected 5 rows with the correct MODEL_NAME. The LEDGER_VIEW is built on top of another view which uses a database link, which is the only potential weirdness I can identify in this query -- it's otherwise very straightforward.
RenderIn
Is there a `CARS` table on the remote DB, and if so does that have duplicates? Not that it should be looking at the remote one anyway, of course.
Alex Poole
+2  A: 

I am unable to recreate via a creation of a stacked view. (althoug RedFilters will find the 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                      ,
        price NUMBER( 10, 4 )             ,
        CONSTRAINT t2_pk PRIMARY KEY( t2_id ),
        CONSTRAINT t2_fk FOREIGN KEY( t1_id ) REFERENCES t1( t1_id )
    );

    insert into t1(t1_id, txt) values(1,'fit');
    insert into t1(t1_id, txt) values(2,'focus');
    insert into t1(t1_id, txt) values(3,'golf');
    insert into t1(t1_id, txt) values(4,'explorer');
    insert into t1(t1_id, txt) values(5,'corolla');

insert into t2(t2_id, t1_id, price) values(1,1,17000);
insert into t2(t2_id, t1_id, price) values(2,2,16000);
insert into t2(t2_id, t1_id, price) values(3,3,22000);
insert into t2(t2_id, t1_id, price) values(4,4,31000);
insert into t2(t2_id, t1_id, price) values(5,5,17000);


create view t1_view as select * from t1;
create view t2_view as select * from t2;
create view t_stacked_view as 
  select t1_view.txt ,
       t2_view.price ,
         t1_view.t1_id
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ;   


--stacked view test
select t1_view.txt ,
       (select t_stacked_view.price 
            from t_stacked_view 
             where t1_view.t1_id = t_stacked_view .t1_id) price
    from t1_view ;

--or better yet, just drop the row level query
select t1_view.txt ,
       t2_view.price
    from t1_view 
          left join
          t2_view 
            on t1_view.t1_id = t2_view .t1_id
    ; 

But that begs the question, why are you doing the row level query here? While 10g ought to optimize them the same, I have always found it easier to write queries as below, both for readability, maintainability, and to specifically avoid the error you are having (is it always, 3 years down the road, guaranteed by the application (both in the db and the calling app) that you cannot have a condition that will cause this error? One rouge statement gets in and your entire app dies?

    SELECT LV.PRICE,
            c.model_name
FROM LEDGER_VIEW LV
      LEFT /* OR INNER */ JOIN CARS C 
       ON C.MODEL_ID = LV.MODEL_ID
WHERE LV.PRICE < 500
tanging
+1: er... I kind of skipped the top half of your question (nested Oracle views are WAY over my head), but I noticed was writing identical SQL to the code at the bottom of your post as a new answer... (oh, I also added the missing word JOIN to the bottom query)
R. Bemrose
@R. Bemrose oops! thanks for adding that for me!
tanging
@tanging: I rewrote the query using an inner join and it works fine now. I just wish I could find an explanation for the error because it makes no sense and that worries me. This is not the first time I've had an issue between these two linked databases that our DBA couldn't explain.
RenderIn
+1  A: 

I suggest using RedFilter's answer to check whether there are multiple cars with a given MODEL_ID.

If you're absolutely certain that CARS.MODEL_ID is unique, then it implies that the error message is generated by selection from LEDGER_VIEW - so try running the equivalent query without the subquery on CARS, like so:

SELECT LV.PRICE
FROM LEDGER_VIEW LV
WHERE LV.PRICE < 500

If you still see the same error (you should, if CARS.MODEL_ID is unique) you will need to debug LEDGER_VIEW - ie. check for sub-queries returning multiple rows in LEDGER_VIEW and the underlying views it is based on.

Creating views based on views is possible in most forms of SQL, but it is usually a bad idea - for this very reason.

Mark Bannister
Selecting from LEDGER_VIEW doesn't give me an error... it's only when I add the subquery joining on MODEL_ID. LEDGER_VIEW joins local tables with some remote tables across a DB Link using the DRIVING_SITE(remote) hint. The error message is being reported by the remote database.
RenderIn
I suggest rerunning the above query with the DRIVING_SITE(remote) hint; the fact that the error message is reported by the remote database appears to confirm that the issue is with LEDGER_VIEW.
Mark Bannister
A: 

Try forcing your subquery to return a single result by appending rownum = 1, like this:

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

It will probably work and if it does, you will know that your subquery returns multiple rows, which judging by the error code it should be. Of course this is not a solution so you might have to fix your data in cars table to actually solve the problem. Leaving and rownum = 1 will eliminate the error if model_id is duplicated again, preventing you from noticing the problem.

vls
`rownum = 1` won't do it, you should use `rownum < 2`
be here now
@be here now:`rownum = 1` works. It's only when you try to use a value greater than one that `rownum = x` doesn't work.
Allan
rownum = 1 and rownum < 2 are equivalent since row numbering in Oracle starts with 1 and grows upwards.
vls