/*================================================================ update lbs_tmp_prem_info_fysp T1 set DEPTNO2 = nvl((select T2.deptno from lbs_agt_perf_trace T2 where T1.ACTUAL_AGENTNO = T2.EMPNO and trunc(T1.Payment_Date,'mm') = T2.Start_Date),T1.Deptno2) where T1.Payment_Date >= to_date(to_char(200301),'yyyymm') and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1) and T1.deptno like to_char(101)||'%'; /*================================================================*/ 的结果只有一条记录 对于过程里面的有多条记录 NVL(数组,2)可能有问题
SET语句后是不是返回了多条结果集???有多条记录用CURSOR实现
nvl放到里面update lbs_tmp_prem_info_fysp T1 set DEPTNO2 = (select nvl(T2.deptno,T1.Deptno2) from lbs_agt_perf_trace T2 where T1.ACTUAL_AGENTNO = T2.EMPNO and trunc(T1.Payment_Date,'mm') = T2.Start_Date), where T1.Payment_Date >= to_date(to_char(200301),'yyyymm') and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1) and T1.deptno like to_char(101)||'%';
重新贴一下 update lbs_tmp_prem_info_fysp T1 set DEPTNO2 = (select nvl(T2.deptno,T1.Deptno2) from lbs_agt_perf_trace T2 where T1.ACTUAL_AGENTNO = T2.EMPNO and trunc(T1.Payment_Date,'mm') = T2.Start_Date) where T1.Payment_Date >= to_date(to_char(200301),'yyyymm') and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1) and T1.deptno like to_char(101)||'%';
update lbs_tmp_prem_info_fysp T1 set DEPTNO2 = (select nvl(max(T2.deptno),T1.Deptno2) from lbs_agt_perf_trace T2 where T1.ACTUAL_AGENTNO = T2.EMPNO and trunc(T1.Payment_Date,'mm') = T2.Start_Date);
update lbs_tmp_prem_info_fysp T1
set DEPTNO2 =
nvl((select T2.deptno
from lbs_agt_perf_trace T2
where T1.ACTUAL_AGENTNO = T2.EMPNO
and trunc(T1.Payment_Date,'mm') = T2.Start_Date),T1.Deptno2)
where T1.Payment_Date >= to_date(to_char(200301),'yyyymm')
and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1)
and T1.deptno like to_char(101)||'%';
/*================================================================*/
的结果只有一条记录
对于过程里面的有多条记录 NVL(数组,2)可能有问题
set DEPTNO2 =
(select nvl(T2.deptno,T1.Deptno2)
from lbs_agt_perf_trace T2
where T1.ACTUAL_AGENTNO = T2.EMPNO
and trunc(T1.Payment_Date,'mm') = T2.Start_Date), where T1.Payment_Date >= to_date(to_char(200301),'yyyymm')
and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1)
and T1.deptno like to_char(101)||'%';
update lbs_tmp_prem_info_fysp T1
set DEPTNO2 =
(select nvl(T2.deptno,T1.Deptno2)
from lbs_agt_perf_trace T2
where T1.ACTUAL_AGENTNO = T2.EMPNO
and trunc(T1.Payment_Date,'mm') = T2.Start_Date)
where T1.Payment_Date >= to_date(to_char(200301),'yyyymm')
and T1.Payment_Date < add_months(to_date(to_char(200301),'yyyymm'),1)
and T1.deptno like to_char(101)||'%';
set DEPTNO2 = (select nvl(max(T2.deptno),T1.Deptno2) from lbs_agt_perf_trace T2 where T1.ACTUAL_AGENTNO = T2.EMPNO and trunc(T1.Payment_Date,'mm') = T2.Start_Date);
nvl放在里边是不行的(我以前试过),
如果是多个返回值的话,为什么不再过程中就可以呢?
我一直奇怪为什么?