1.proc编译问题
UPDATE TFUNDDAY A
SET (F_INCOME)=
NVL((SELECT NVL(A.F_INCOME,0)+NVL(B.f_DepositAgmIncome,0)+NVL(B.f_DepositRdmIncome,0)
FROM TFUNDDAY B
WHERE B.C_FUNDCODE=A.C_FUNDCODE
AND B.D_CDATE=TO_DATE(:GRequestDate,'YYYYMMDD')),F_INCOME)
WHERE A.D_CDATE=TO_DATE(:GConfirmDate,'YYYYMMDD')
AND EXISTS (SELECT 1
FROM TQSSPECIALPRODUCT C
WHERE C.C_PRODUCTTYPE='0'
AND C.C_ISCLASSFLAG='1'
AND C.C_FUNDCODE=A.C_FUNDCODE);
这条sql语句可以在plsql上面跑，但是proc编译通不过,编译出错原因是SET后面的第一个NVL这个地方有问题
2.外连接问题
前提条件，行情表中10221022的行情不存在，20121025的行情未付收益F_INCOME为10000，其他(f_DepositRdmIncome和f_DepositRdmIncome)的都为空值
UPDATE TFUNDDAY A
SET (F_INCOME)=
(SELECT NVL(A.F_INCOME,0)+NVL(B.f_DepositAgmIncome,0)+NVL(B.f_DepositRdmIncome,0)
FROM TFUNDDAY B
WHERE B.C_FUNDCODE(+)=A.C_FUNDCODE
AND B.D_CDATE(+)=TO_DATE('20121022','YYYYMMDD'))
WHERE A.D_CDATE=TO_DATE('20121025','YYYYMMDD')
AND EXISTS (SELECT 1
FROM TQSSPECIALPRODUCT C
WHERE C.C_PRODUCTTYPE='0'
AND C.C_ISCLASSFLAG='1'
AND C.C_FUNDCODE=A.C_FUNDCODE);
运行完成后，查询20121025的行情未付收益为空
SELECT NVL(A.F_INCOME,0)+NVL(B.f_DepositAgmIncome,0)+NVL(B.f_DepositRdmIncome,0)
FROM TFUNDDAY B,TFUNDDAY A
WHERE B.C_FUNDCODE(+)=A.C_FUNDCODE
AND B.D_CDATE(+)=TO_DATE('20121022','YYYYMMDD')
AND A.D_CDATE=TO_DATE('20121025','YYYYMMDD')
AND EXISTS (SELECT 1
FROM TQSSPECIALPRODUCT C
WHERE C.C_PRODUCTTYPE='0'
AND C.C_ISCLASSFLAG='1'
AND C.C_FUNDCODE=A.C_FUNDCODE);
查询结果为10000
查询语句是根据update语句写出来的，条件完全一样，为什么查询出来的结果跟更新的结果不一样
阅读(1396) | 评论(0) | 转发(0) |
