我正在重写一段旧代码。当我在select a.*块中添加了以下内容时,我得到了以下错误
FROM USB.DFP_DATA A, BAS2_DFP_OBLIGOR_FACTS C
where a.as_of_date = v_date and a.DFPDEAL_TAX_ID_NBR=c.DFPDEAL_TAX_ID_NBR(+) and a.as_of_Date = c.as_of_date(+)
您能告诉我如何解决这个错误吗?提前感谢您的帮助!
--new code. user will enter INSTTABLE and date and it should run and update fields.
IF INSTTABLE = 5 THEN
V_BASEL_MIN :=0.08;
V_BAS_RWA_RATE:=BAS_RWA_ADJ_RATE(V_DATE);
UPDATE ( SELECT A.*,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(CUR_BOOK_BAL,0), NVL(BAS_CAP_FACTOR_K,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_EB_RWA,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(CUR_BOOK_BAL,0), NVL(BAS_ICAAP_FACTOR_K,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_EB_RWA,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(AVG_BOOK_BAL,0), NVL(BAS_CAP_FACTOR_K,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_AB_RWA,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(BAS_UNUSED_COMMT,0), NVL(BAS_CAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_EB_RWA_COMMT,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(BAS_UNUSED_COMMT,0), NVL(BAS_ICAAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_EB_RWA_COMMT,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD, NVL(BAS_UNUSED_COMMT,0), NVL(BAS_CAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_AB_RWA_COMMT,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD,NVL(BAS_PART_UNUSED_COMMT,0), NVL(BAS_CAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_PART_EB_RWA_COMMT,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD,NVL(BAS_PART_UNUSED_COMMT,0), NVL(BAS_ICAAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_EB_RWA_PART,
BAS2_RWA_CALC(BAS_CAPITAL_CALC_CD,NVL(BAS_PART_UNUSED_COMMT,0), NVL(BAS_CAP_FACTOR_K_COMMT,0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_PART_AB_RWA_COMMT
FROM USB.DFP_DATA A, BAS2_DFP_OBLIGOR_FACTS C
where a.as_of_date = v_date and a.DFPDEAL_TAX_ID_NBR=c.DFPDEAL_TAX_ID_NBR(+) and a.as_of_Date = c.as_of_date(+))
SET
BAS_EB_RWA = V_BAS_EB_RWA,
BAS_ICAAP_EB_RWA = V_BAS_ICAAP_EB_RWA,
BAS_AB_RWA = V_BAS_AB_RWA,
BAS_EB_RWA_COMMT = V_BAS_EB_RWA_COMMT,
BAS_ICAAP_EB_RWA_COMMT = V_BAS_ICAAP_EB_RWA_COMMT,
BAS_AB_RWA_COMMT = V_BAS_AB_RWA_COMMT,
BAS_PART_EB_RWA_COMMT = V_BAS_PART_EB_RWA_COMMT,
BAS_ICAAP_EB_RWA_PART = V_BAS_ICAAP_EB_RWA_PART,
BAS_PART_AB_RWA_COMMT = V_BAS_PART_AB_RWA_COMMT,
BAS_EB_EXPECTED_LOSS = BAS2_EL_CALC(CUR_BOOK_BAL,bas_unused_commt,BAS_PD,BAS_LGD,BAS_LEQ,BAS_EAD,BAS_CAPITAL_CALC_CD),
BAS_AB_EXPECTED_LOSS = BAS2_EL_CALC(AVG_BOOK_BAL,bas_unused_commt,BAS_PD,BAS_LGD,BAS_LEQ,BAS_EAD,BAS_CAPITAL_CALC_CD),
BAS_EB_TOTAL_CAPITAL = round(BAS2_MGRL_CAPITAL(v_date, v_BAS_EB_RWA,v_BAS_EB_RWA_COMMT),2),
BAS_AB_TOTAL_CAPITAL = round(BAS2_MGRL_CAPITAL(v_date, v_BAS_AB_RWA,v_BAS_AB_RWA_COMMT),2),
BAS_PART_EB_EXPECTED_LOSS= BAS2_EL_CALC(0,bas_part_unused_commt,BAS_PD,BAS_LGD,BAS_LEQ,BAS_EAD,BAS_CAPITAL_CALC_CD),
BAS_PART_AB_EXPECTED_LOSS= BAS2_EL_CALC(0,bas_part_unused_commt,BAS_PD,BAS_LGD,BAS_LEQ,BAS_EAD,BAS_CAPITAL_CALC_CD),
BAS_PART_EB_TOTAL_CAPITAL= round(BAS2_MGRL_CAPITAL(v_date, 0,nvl(v_BAS_PART_EB_RWA_COMMT,0)),2),
BAS_PART_AB_TOTAL_CAPITAL= round(BAS2_MGRL_CAPITAL(v_date, 0,nvl(v_BAS_PART_AB_RWA_COMMT,0)),2)
WHERE
(AS_OF_DATE = V_DATE);
END IF;
期望的--它应该运行并更新SET块中提到的字段
发布于 2019-06-27 23:31:19
不幸的是,您很可能最终会用更现代的MERGE
语法重写它,或者以不同的方式进行更新。
您的代码将内联视图用作可更新的视图,并且对于何时可以更新视图有一些限制。
Oracle docs on views, (search for "Notes on updatable views")
https://stackoverflow.com/questions/56794220
复制相似问题