Home » SQL & PL/SQL » SQL & PL/SQL » single-row subquery returns more than one row (11)
single-row subquery returns more than one row [message #571365] Sat, 24 November 2012 16:16 Go to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
I am getting teh following error message:
"single-row subquery returns more than one row"

how to get just the top 1 row.

I have select query with lot of sub queries within.

select p_al.alias PatID,e_al.alias VisitID, enc.location_cd as LocID,
(select display from code_value where enc.location_cd=code_value.code_value and code_value.code_set=220 and
code_value.active_ind=1) as ServLocName,enc.med_service_cd MedGrpID,

Thanks a lot for the helpful info.
Re: single-row subquery returns more than one row [message #571366 is a reply to message #571365] Sat, 24 November 2012 16:21 Go to previous messageGo to next message
BlackSwan
Messages: 22521
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

rows in a table are like balls in a basket.
Which ball in the "top" ball?

What specifically differentiates "top" row from all the other rows?

Re: single-row subquery returns more than one row [message #571367 is a reply to message #571365] Sat, 24 November 2012 16:55 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
cplusplus1 wrote on Sat, 24 November 2012 23:16
... how to get just the top 1 row ...

According to Oracle® Database Concepts 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#i20438
Quote:

...
By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

Note: Index-organized tables use a different principle of organization. See "Overview of Index-Organized Tables".
...



Regards,
Dariyoosh
Re: single-row subquery returns more than one row [message #571375 is a reply to message #571365] Sun, 25 November 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to get just the top 1 row.


Do NOT use subquery in SELECT until you'll be an expert in SQL.

Modify your subquery so that you get only one row.
If what you want if 0 or 1 row, then your subquery is wrong.
Only YOU know your model, tables and data.

Regards
Michel

Re: single-row subquery returns more than one row [message #571420 is a reply to message #571365] Sun, 25 November 2012 20:20 Go to previous messageGo to next message
tony123
Messages: 9
Registered: August 2012
Junior Member

select p_al.alias PatID,e_al.alias VisitID, enc.location_cd as LocID,
(select display from code_value where enc.location_cd=code_value.code_value and code_value.code_set=220 and
code_value.active_ind=1 and rownum=1) as ServLocName,enc.med_service_cd MedGrpID,
Re: single-row subquery returns more than one row [message #571429 is a reply to message #571420] Sun, 25 November 2012 23:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very BAD solution as undeterministic (unless all returned rows/values are identical).

Regards
Michel
Re: single-row subquery returns more than one row [message #571441 is a reply to message #571365] Mon, 26 November 2012 01:18 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
you should first determine which set of records produce multiple yields in your sub query by using count function and

  select x.code_value ,
               count(display)
          from code_value  x
         where x.code_set=220 
           and x.active_ind=1
       group by  code_value
          having count(display) >1


after identifying the records then determine if this is allowed by your business rules if so then you must decide what kind of query you want to have.

if you want to maintain the number of records of your main query then you may just concatenate those data with multiple records
using function like LISTAGG

or if not you may just link this table to your main query but expect that your number of records (main query) will increase due to multiple records found in this table.

you may also use distinct or rownum = 1 if you are sure that all the values will always be similar .

[Updated on: Mon, 26 November 2012 01:23]

Report message to a moderator

Re: single-row subquery returns more than one row [message #571445 is a reply to message #571441] Mon, 26 November 2012 01:33 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member

sorry I was not able to read that what you want is the top row
what will be your basis of the top 1 row? the primary key of the table? the length of the display column? you may use analytical function rank in that case
Re: single-row subquery returns more than one row [message #571446 is a reply to message #571445] Mon, 26 November 2012 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Top" is meaningless unless an order is defined.

Regards
Michel
Re: single-row subquery returns more than one row [message #571447 is a reply to message #571446] Mon, 26 November 2012 01:45 Go to previous message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
that's why I'm asking him the basis for top.
Previous Topic: encoding using UTL_I18N.ESCAPE_REFERENCE
Next Topic: merge rows
Goto Forum:
  


Current Time: Tue Jul 29 11:22:21 CDT 2014

Total time taken to generate the page: 0.06991 seconds