CODEWITHSUNDEEP
oracle-databaseoracle10g
Awlad Liton
Awlad Liton

Reputation: 9351

oracle query error: exact fetch return more than requested no of rows

I have two tables seatinfo(siid,seatno,classid,tsid) and booking (bookid,siid,date,status).

I've input parameter bookDate,v_tsId ,v_clsId. I need exactly one row (bookid) to return. This query is not working. I don't no why. How can I fix it?

 select bookid 
   into v_bookid 
    from booking 
   where (to_char(booking.bookdate,'dd-mon-yy'))=(to_char(bookDate,'dd-mon-yy')) 
     and status=0 
     and rownum <= 1 
     and siid in(select siid 
                   from seatinfo 
                  where tsid=v_tsId 
                   and classid= v_clsId);

I also tried this: 

select bookid 
  into v_bookid 
  from booking,
       seatinfo 
 where booking.siid=seatinfo.siid 
   and (to_char(booking.bookdate,'dd-mon-yy'))=(to_char(bookDate,'dd-mon-yy')) 
   and booking.status=0 
   and rownum <= 1 
   and seatinfo.tsid=v_tsId 
   and seatinfo.classid= v_clsId;

Upvotes: 0

Views: 599

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

Are you saying that you get an "ORA-01422: exact fetch returns more than requested number of rows" when you run both of those queries? That seems highly unlikely since you're including the predicate rownum <= 1. Can you cut and paste from a SQL*Plus session that runs just this query in a PL/SQL block and generates the error?

If you are not complaining about the error you mention in the title, and the problem is just that you're not getting the data you expect, the likely problem is that you apparently have a bookDate parameter that has the same name as a column in your table. That is not going to work. When you say

(to_char(booking.bookdate,'dd-mon-yy'))=(to_char(bookDate,'dd-mon-yy'))

you presumably mean to compare the bookDate column in the booking table against the bookDate parameter. But since column names have precedence over local variables, the left-hand side of your expression is also looking at the bookDate column in the booking table. So you're comparing a column to itself. It would make much more sense to change the name of the parameter (to, say, p_bookDate) and then write

booking.bookDate = p_bookDate

or, if you want to do the comparison ignoring the time component of the dates

trunc( booking.bookDate ) = trunc( p_bookDate )

Upvotes: 2

Related Questions