用merge into 的时候提示 ORA-01733: virtual column not allowed here

最新推荐文章于 2021-01-30 11:52:48 发布

原创最新推荐文章于 2021-01-30 11:52:48 发布 · 4.7k 阅读

0 ·

CC 4.0 BY-SA版权

本文解决了一个在使用Oracle数据库时遇到的问题：ORA-01733错误。该错误出现在尝试使用MERGE INTO语句更新数据时。通过调整SQL语句并正确引用表字段而非变量，成功解决了这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Error: PL/SQL: ORA-01733: virtual column not allowed here
Line: 81
Text: MERGE INTO SvcOrderProfit

Error: PL/SQL: SQL Statement ignored
Line: 81

Text: MERGE INTO SvcOrderProfit

用merge into 的时候提示

REF ：http://www.cnblogs.com/dongsheng/p/4384754.html

问题代码：

WHEN MATCHED THEN
UPDATE
SET IncomeRMB = pos_2,
CostRMB = pos_3;/*,
v_dIncomeUSDFee = pos_4,
v_dIncomeUSDAccount = pos_5,
v_dlCostUSDFee = pos_6,
v_dlCostUSDAccount = pos_7,
v_dIncomeRMBSales = pos_8,
v_dCostRMBSales = pos_9,

ROOT CAUSE 本来左边应该是表名，从SQL 转过来的时候却弄成了变量。所以报错

：

solution :

表字段的保留，非表字段的SELECT INTO 到变量里面：

SELECT NVL((SELECT SUM(SvcOrderFee.NoTaxFeeAmount)
FROM SvcOrderFee
WHERE SvcOrderFee.CurrencyType = 0
AND SvcOrderFee.CreditDebitType = 0
AND (SvcOrderFee.AccountType = 2 OR
SvcOrderFee.AccountType = 3)
AND SvcOrderFee.OrderBillID = v_lOrderBillID),
0)

INTO v_dCostRMBSales
FROM DUAL;