sql子查询的子查询内部无法使用最外层的参数的问题

					(case
                         when (select distinct 1
                                 from lcinshold
                                where hangupreason in ('07', '08')
                                  and canceloperator is null
                                  and insuredid = a.insuredid) is not null then
                          /*(select ',' || wm_concat(aa)
                             from (select (select codename
                                             from ldcode
                                            where codetype = 'holdupreason'
                                              and code = hangupreason) aa
                                     from lcinshold
                                    where hangupreason in ('07', '08')
                                      and canceloperator is null
                                      and insuredid = a.insuredid))*/
                                       ( select ',' || wm_concat(aa)  from (select (select codename  from ldcode 
                                        where codetype = 'holdupreason' and code = hangupreason) aa , insuredid  from 
                                        lcinshold where hangupreason in ('07', '08') and canceloperator is null  ) 
                                        where   insuredid = a.insuredid  )
                      else
                          ' '
                      end) 

注释起来的是错误的 a.insuredid 无法找到
下面是解决的.

最外层的参数无法传递到子查询的子查询.
用这种方法解决

同理

update liinscontact c
   set c.occupation =
      (select * from  (select occupation
          from lcinsured
         where effectivedate < expirydate
           and occupation is not null
           and customid = c.customid
           and employeeid = c.employeeid
           and rownum = 1
         order by effectivedate desc, modifydate desc))

         -- 需要的执行时间: 93秒

改为:

update liinscontact c
   set c.occupation =
       (select occupation
          from (select occupation,customid ,employeeid 
                  from lcinsured
                 where effectivedate < expirydate and occupation is not null 
                 order by effectivedate desc, modifydate desc)
        
         where customid = c.customid
           and employeeid = c.employeeid
           and rownum = 1
        
        ) -- 需要的执行时间: 93秒