Home » SQL & PL/SQL » SQL & PL/SQL » Single Row Subquery returns more than one row... Help with query (10gR2)
Single Row Subquery returns more than one row... Help with query [message #388908] Thu, 26 February 2009 10:32 Go to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
When I run the query below I get the error single row subquery returns more than one row. I tried running the subquery individually and I get one row back... please help as I think from starring at the query and data for too long now and I am stuck in this one solution. I appreciate your recommendations for solutions already.


Table DSC_USER
ID First_Name Last_name Email Registration_date
u140449132 Test Tester Tester@phone.com 2/6/2009 3:55:53 PM


Table DSC_USER_ADDRESS
ID SHIPPING_ADDR_ID BILLING_ADDR_ID
u140449132 cti400587424 cti400587423


Table DSC_CONTACT_INFO
ID FIRST_NAME LAST_NAME ADDRESS1 ADDRESS2 CITY STATE ZIP COUNTRY
cti400587424 Test Tester 120 Carter Street Ahoskie NC 27858 US
cti400587423 Test Tester 222 Fulton Ave Suite 20 Greensboro NC 28214 US






select a.first_name, a.last_name, a.email, a.registration_date,
(select c.address1||' '||c.address2||' '||c.city||' '||c.state
from dsc_contact_info c,
dsc_user_address b,
dsc_user a
where b.billing_addr_id = c.id
and a.id = b.id )"Billing Address",
(select c.address1||c.address2||' '||c.city||' '||c.state
from dsc_contact_info c,
dsc_user_address b,
dsc_user a
where b.shipping_addr_id = c.id
and a.id=b.id)"Shipping Address"
from dsc_user a,
dsc_user_address b,
dsc_contact_info c
where a.id = b.id
and a.email = 'tester@phone.com'
and c.id = b.billing_addr_id
or c.id = b.shipping_addr_id



from dsc_contact_info c,

ORA-01427: single-row subquery returns more than one
row
Re: Single Row Subquery returns more than one row... Help with query [message #388909 is a reply to message #388908] Thu, 26 February 2009 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realize that since you're using the exact same set of table aliases (a, b and c) in both sub-querys AND the outer query you have basically got NO links between the sub-querys and the outer query?

Also can you please read the orafaq forum guide and learn how to use code tags and write a test case.
Re: Single Row Subquery returns more than one row... Help with query [message #388914 is a reply to message #388908] Thu, 26 February 2009 11:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I didn't had any issue running this sql.
with dsc_user as
(
 select 'u140449132' id , 'Test' first_name,  'Tester' last_name , 'Tester@phone.com' email, sysdate registration_date from dual
),
dsc_user_Address as
(
 select 'u140449132' id,  'cti400587424' shipping_addr_id , 'cti400587423' billing_addr_id from dual
),
dsc_contact_info as
(
 select 'cti400587424' id, 'Test' first_name,  'Tester' last_name,  120 address1,  'Carter Street' Address2, 'Ahoskie' city, 'NC' state,  27858 zip, 'US' country from dual union all
select 'cti400587423','Test','Tester',222, 'Fulton Ave Suite', '20 Greensboro', 'NC', 28214, 'US' from dual
)
select a.first_name, a.last_name, a.email, a.registration_date,
(select c.address1||' '||c.address2||' '||c.city||' '||c.state 
from dsc_contact_info c,
dsc_user_address b,
dsc_user a
where b.billing_addr_id = c.id
and a.id = b.id )"Billing Address",
(select c.address1||c.address2||' '||c.city||' '||c.state 
from dsc_contact_info c,
dsc_user_address b,
dsc_user a
where b.shipping_addr_id = c.id
and a.id=b.id)"Shipping Address"
from dsc_user a,
dsc_user_address b,
dsc_contact_info c
where a.id = b.id
and a.email = 'tester@phone.com'
and c.id = b.billing_addr_id
or c.id = b.shipping_addr_id


Regards

Raj
Re: Single Row Subquery returns more than one row... Help with query [message #389355 is a reply to message #388908] Sun, 01 March 2009 07:59 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Execute this query, then you'll understand!

select a.first_name,a.last_name,a.email,a.registration_date,(
		select count(*)
		from dsc_contact_info c,
		dsc_user_address b,
		dsc_user a
		where b.billing_addr_id = c.id
			and a.id = b.id 
	)"Billing Address",(
		select count(*)
		from dsc_contact_info c,
		dsc_user_address b,
		dsc_user a
		where b.shipping_addr_id = c.id
			and a.id=b.id
	)"Shipping Address"
from dsc_user a,
	dsc_user_address b,
	dsc_contact_info c
where a.id = b.id
	and a.email = 'tester@phone.com'
	and c.id = b.billing_addr_id
	or c.id = b.shipping_addr_id


Anyway I've not idea of what're trying to get but in my opinion you've developed a wrong and buggy query that doesn't do what you may except from it.

Bye Alessandro
Previous Topic: Multiple Recipients in SMTP
Next Topic: -SQL -Not starting
Goto Forum:
  


Current Time: Sat Dec 10 20:42:10 CST 2016

Total time taken to generate the page: 0.07291 seconds