Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00913: too many values
ORA-00913: too many values [message #599210] Wed, 23 October 2013 00:53 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

Can any one please help me on this Query and when i ran this below query it shows an error: ORA-00913: too many values
select * from oe_order_lines_all 
where header_id in(select * FROM oe_order_headers_all  where order_number=4721)


Thank you
Re: ORA-00913: too many values [message #599211 is a reply to message #599210] Wed, 23 October 2013 00:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3146
Registered: May 2013
Location: World Wide on the Web
Senior Member
Since the subquery return all the columns from table oe_order_headers_all instead of just header_id.
Re: ORA-00913: too many values [message #599212 is a reply to message #599211] Wed, 23 October 2013 01:00 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks for quick reply,

select * from oe_order_lines_all 
where header_id in(select header_id FROM oe_order_headers_all  where order_number=4721)


Is this correct?

Thank you
Re: ORA-00913: too many values [message #599213 is a reply to message #599212] Wed, 23 October 2013 01:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3146
Registered: May 2013
Location: World Wide on the Web
Senior Member
Syntactically it is correct unless you show us some error. Does it give you the required output?
Re: ORA-00913: too many values [message #599214 is a reply to message #599213] Wed, 23 October 2013 01:04 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
I think yes it gives and i think that the sub query returns header_id from the headers table to the outer query is this correct?
Otherwise please suggest me.

Thank you
Re: ORA-00913: too many values [message #599216 is a reply to message #599214] Wed, 23 October 2013 01:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3146
Registered: May 2013
Location: World Wide on the Web
Senior Member
mist598 wrote on Wed, 23 October 2013 11:34
I think yes it gives


You think? Just validate the output.

Quote:
and i think that the sub query returns header_id from the headers table to the outer query is this correct?


Yes the subquery returns all the values of header_id for order_number "4721", so it should work fine.
Re: ORA-00913: too many values [message #599217 is a reply to message #599214] Wed, 23 October 2013 01:16 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi mist,

Your subquery will pass header id from subquery to outer query and it will give you matching header_id results.It will work like
equijoin.

Please use like this for getting better performance:


select   a.* 
from     oe_order_lines_all   a,
         oe_order_headers_all b 
where    b.order_id = 4721
and      a.header_id = b.header_id



[Updated on: Wed, 23 October 2013 01:16]

Report message to a moderator

Re: ORA-00913: too many values [message #599218 is a reply to message #599217] Wed, 23 October 2013 01:18 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Thank you very much both of you.

Regards,
Mist Smile
Re: ORA-00913: too many values [message #599222 is a reply to message #599217] Wed, 23 October 2013 01:32 Go to previous messageGo to next message
Littlefoot
Messages: 21482
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
msol25 wrote on Wed, 23 October 2013 08:16

Please use like this for getting better performance:


select   a.* 
from     oe_order_lines_all   a,
         oe_order_headers_all b 
where    b.order_id = 4721
and      a.header_id = b.header_id





Well, not really. As B's columns aren't part of the SELECT column list, that table should be moved into a WHERE clause as a subquery (just as the original query looks like).
Re: ORA-00913: too many values [message #599224 is a reply to message #599222] Wed, 23 October 2013 01:38 Go to previous message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Yes i understood and thanks for help me Littlefoot.

Regards,
Mist Smile
Previous Topic: dropdown
Next Topic: Mysterious behaviour of Oracle SQL
Goto Forum:
  


Current Time: Mon Jun 24 23:11:12 CDT 2019