Correllated Sub-query problem--Help!

From: Jonathan G Gennick <gennick_at_worldnet.att.net>
Date: 1997/03/03
Message-ID: <331b60e5.1528672_at_netnews.worldnet.att.net>#1/1

I can successfully execute this query:

SQL> select agr_agree_nbr
  2 from agr_agreement aa

  3   where nvl(agr_inv_diff_amt,0) <= (                
  4                  select sum(osp_type_amount) as payment_amount
  5                    from osp_oper_sub_payment
  6                   where osp_pay_type_cd = 2
  7                     and osp_pay_type_num = aa.agr_agree_nbr
  8                   );

AGR_AGREE_NBR

---

           51

However, I can NOT successfully execute this:

SQL> select agr_agree_nbr
  2 from agr_agreement aa
  3 where nvl(agr_inv_diff_amt,0) <= (

  4               select sum(payment_amount) from 
  5                  (
  6                  select sum(osp_type_amount) as payment_amount
  7                    from osp_oper_sub_payment
  8                   where osp_pay_type_cd = 2
  9                     and osp_pay_type_num = aa.agr_agree_nbr
 10                   )
 11  );
                   and osp_pay_type_num = aa.agr_agree_nbr
                                             *

ERROR at line 9:
ORA-00904: invalid column name

The only difference between the two is that I have nested one more subquery level. What's going on? Why won't this work? I need to be able to select the sum of a UNION, i.e.

     and nvl(agr_inv_diff_amt,0) <= ( 
         select nvl(sum (payment_amount),0) 
           from (
                select sum(osp_type_amount) as payment_amount
                  from osp_oper_sub_payment
                 where osp_pay_type_cd = &shortage
                   and osp_pay_type_num = agr_agree_nbr
                union 
                select sum(rep_total_amount) as payment_amount
                  from rep_repayment_agree
                 where rep_pay_type_cd = &shortage
                   and rep_pay_type_num = agr_agree_nbr 
                )
         )  select sum(payment_amount)

Any help would be appreciated.

thanks,

Jonathan Received on Mon Mar 03 1997 - 00:00:00 CET