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  |
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 #388914 is a reply to message #388908] |
Thu, 26 February 2009 11:15   |
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  |
 |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 06 17:12:23 CST 2025
|