CODEWITHSUNDEEP
sqloracle-databaseplsql
Brad
Brad

Reputation: 35

subquery returns more than one row

I am having trouble with the following SQL Statement. I am getting back an error saying "ORA-01427:single-row subquery returns more than one row" and I'm not exactly sure how to fix the error. Any help is greatly appreciated! Thank you, Brad

select t.check_amount,
   t.check_amount_text,
   t.agency_no,
   t.branch_no,
   substr(t.payee_name,1,10),
   substr(t.payee_name,11),
   (select c.contact_primary_email 
    from contact c,
         contact_role cr,
         branch b
    where c.contact_id = cr.contact_id
          and cr.entity_id = b.branch_id
          and cr.contact_role_code = 'ACHCONTACT')

from CHECK_REGISTER t
where t.check_type = 'C' 
  and t.check_date > to_date('01/31/2014','mm/dd/yyyy')
and t.disbursement_fund_type = 'ACH'

Upvotes: 1

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The error is occurring because you have a select statement where a single value is expected. The select is returning more than one row, so Oracle reports an error.

In a query such as yours, this is usually because you need to correlate the inner subquery to the outer query. My best guess is that this query wants the email address of the primary contact at the branch. For this, the query would be:

   (select c.contact_primary_email 
    from contact c join
         contact_role cr
         on c.contact_id = cr.contact_id join
         branch b
         on cr.entity_id = b.branch_id
    where b.branch_no = t.branch_no and cr.contact_role_code = 'ACHCONTACT'
   ) as email

This postulates that the correlation condition on branch_no.

Upvotes: 3

Related Questions