CODEWITHSUNDEEP
sqloracle-databaseselect
user2744565
user2744565

Reputation: 243

SQL error sign (single-row subquery returns more than one row

I got this "single-row subquery returns more than one row"

and my Query is 

Select contact_ID from contact where (

Select b.Contact_ID from company a, contact b where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE >= '01-Jun-2013') =
(
Select b.Contact_ID from company a, contact b where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE < '01-Jun-2013');

I don't know why this is not working. Please help me out

Upvotes: 0

Views: 1250

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

    Select contact_ID from contact where Contact_ID in 
        (
         Select b.Contact_ID from company a 
         inner join contact b 
         on  a.Company_ID =  b.Company_ID 
         where a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
         and a.CREATED_DATE >= '01-Jun-2013' 
         ) ;

edit:

more simpler without IN clause.

     Select b.Contact_ID from company a 
     inner join contact b 
     on  a.Company_ID =  b.Company_ID 
     where a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
     and a.CREATED_DATE >= '01-Jun-2013' ;

Upvotes: 1

Srini V
Srini V

Reputation: 11355

Try this:

Removed sub select and replaced with union. 

Select b.Contact_ID 
from company a, contact b 
where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE >= '01-Jun-2013'
union
Select b.Contact_ID 
from company a, contact b 
where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE < '01-Jun-2013');

Upvotes: 0

Srini V
Srini V

Reputation: 11355

Try this

Select contact_ID 
from contact 
where (
Select b.Contact_ID 
from company a, contact b 
where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE >= '01-Jun-2013') IN
(
Select b.Contact_ID 
from company a, contact b 
where a.Company_ID =  b.Company_ID 
and a.IS_ACTIVE <> 'N' and b.IS_ACTIVE <> 'N'
and a.CREATED_DATE < '01-Jun-2013');

Upvotes: 0

Related Questions