Correlated Subquery Question??

[lesstjm]

I am trying to write a correlated subquery. I want the parent query to consult the subquery on every row but it isn't working. The subquery is returning two rows instead of one row for each parent record. Were is my error???


1 select a.acct_no acct,range.category cat,
2 a.montran_ref montra,(a.amt-b.amt) amt, a.dr_name dr, b.cr_name cr
3 from hsbcloader.txn a, hsbcloader.txn b, business.range
4 where a.montran_ref = b.montran_ref and
5 a.acct_no = '1111111111' and
6 substr(a.payment_type,2) = 'D' and
7 substr(b.payment_type,2) = 'C' and
8 a.value_date >= to_date('01-DEC-1000','DD-MON-YYYY') and
9 a.value_date <= to_date('01-DEC-2020','DD-MON-YYYY') and
10 a.amt > b.amt and
11 category = (select category from business.range where
12 (a.amt-b.amt) >= from_amt and
13* (a.amt-b.amt) <= to_amt)
SQL> /
category = (select category from business.range where
*
ERROR at line 11:
ORA-01427: single-row subquery returns more than one row

[lesstjm]

I figured out my problem. Thanks anyway.