OraFAQ Forum: SQL & PL/SQL » ORA-00913: too many values

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00913: too many values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00913: too many values [message #599210]

Wed, 23 October 2013 00:53

mist598
Messages: 1195
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

Report message to a moderator

Re: ORA-00913: too many values [message #599211 is a reply to message #599210]

Wed, 23 October 2013 00:57

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: ORA-00913: too many values [message #599212 is a reply to message #599211]

Wed, 23 October 2013 01:00

mist598
Messages: 1195
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

Report message to a moderator

Re: ORA-00913: too many values [message #599213 is a reply to message #599212]

Wed, 23 October 2013 01:02

Lalit Kumar B
Messages: 3174
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?

Report message to a moderator

Re: ORA-00913: too many values [message #599214 is a reply to message #599213]

Wed, 23 October 2013 01:04

mist598
Messages: 1195
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

Report message to a moderator

Re: ORA-00913: too many values [message #599216 is a reply to message #599214]

Wed, 23 October 2013 01:15

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: ORA-00913: too many values [message #599217 is a reply to message #599214]

Wed, 23 October 2013 01:16

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

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thank you very much both of you.

Regards,
Mist Smile

Report message to a moderator

Re: ORA-00913: too many values [message #599222 is a reply to message #599217]

Wed, 23 October 2013 01:32

Littlefoot
Messages: 21807
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).

Report message to a moderator

Re: ORA-00913: too many values [message #599224 is a reply to message #599222]

Wed, 23 October 2013 01:38

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Yes i understood and thanks for help me Littlefoot.

Regards,
Mist Smile

Report message to a moderator

Previous Topic:	dropdown
Next Topic:	Mysterious behaviour of Oracle SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:51:42 CDT 2024