sql之ora_rowscn 在 USING 子句中时的 Oracle Merge 语句行为

我有一个 MERGE 语句给我可怕的 ORA-00904: invalid identifier 错误消息。请注意，此处不存在与“无效标识符”错误有关的典型问题 - 我没有尝试更新连接的列，也没有拼错我的列名。我正在尝试在 USING 子句的 SELECT 语句中使用 oracle 伪列 ORA_ROWSCN。

使用这些示例表，我尝试运行

MERGE INTO MY_MERGE_TABLE D 
  USING 
 (SELECT PRIMARY_KEY,  
         COALESCE (UPDATE_DT, CREATED_DT) update_dt, 
         ORA_ROWSCN AS rowscn  
    FROM MY_SOURCE_TABLE) S 
   ON (D.PRIMARY_KEY = S.PRIMARY_KEY) 
WHEN MATCHED THEN 
     UPDATE SET D.update_dt = GREATEST(D.update_dt, S.update_dt), 
                D.rowscn = GREATEST(D.rowscn, S.rowscn) 
WHEN NOT MATCHED THEN 
     INSERT (D.PRIMARY_KEY, D.UPDATE_DT, D.ROWSCN) 
     VALUES (S.PRIMARY_KEY, S.UPDATE_DT, S.ROWSCN); 

如果我从 USING 子句中删除伪列 ora_rowscn，我将不再收到错误消息，并且合并成功完成。

MERGE INTO MY_MERGE_TABLE D 
  USING 
 (SELECT PRIMARY_KEY,  
         COALESCE (UPDATE_DT, CREATED_DT) update_dt 
    FROM MY_SOURCE_TABLE) S 
   ON (D.PRIMARY_KEY = S.PRIMARY_KEY) 
WHEN MATCHED THEN 
     UPDATE SET D.update_dt = GREATEST(D.update_dt, S.update_dt) 
WHEN NOT MATCHED THEN 
     INSERT (D.PRIMARY_KEY, D.UPDATE_DT) 
     VALUES (S.PRIMARY_KEY, S.UPDATE_DT); 

如果我改为将查询放入 VIEW，我就可以成功使用 ora_rowscn:

CREATE VIEW MY_VIEW AS  
    SELECT PRIMARY_KEY,  
         COALESCE (UPDATE_DT, CREATED_DT) update_dt, 
         ORA_ROWSCN AS rowscn  
    FROM MY_SOURCE_TABLE; 
 
MERGE INTO MY_MERGE_TABLE D 
  USING (SELECT PRIMARY_KEY, UPDATE_DT, ROWSCN FROM MY_VIEW) S 
   ON (D.PRIMARY_KEY = S.PRIMARY_KEY) 
WHEN MATCHED THEN 
     UPDATE SET D.update_dt = GREATEST(D.update_dt, S.update_dt), 
                D.rowscn = GREATEST(D.rowscn, S.rowscn) 
WHEN NOT MATCHED THEN 
     INSERT (D.PRIMARY_KEY, D.UPDATE_DT, D.ROWSCN) 
     VALUES (S.PRIMARY_KEY, S.UPDATE_DT, S.ROWSCN); 

有没有办法在不为查询创建 VIEW 的情况下做到这一点？作为 ETL 过程的一部分，我必须在许多表上执行此操作，并且不想构建多个 View 。

编辑: 根据 Glenn 在评论中的建议，我尝试将查询放入子查询中:

MERGE INTO MY_MERGE_TABLE D 
  USING 
 (WITH QRY AS  
  (SELECT PRIMARY_KEY,  
         COALESCE (UPDATE_DT, CREATED_DT) update_dt, 
         ORA_ROWSCN AS rowscn  
    FROM MY_SOURCE_TABLE) 
  SELECT ORDER_ID, UPDATE_DT, ROWSCN FROM QRY) 
 ON (D.PRIMARY_KEY = S.PRIMARY_KEY) 
WHEN MATCHED THEN 
     UPDATE SET D.update_dt = GREATEST(D.update_dt, S.update_dt), 
                D.rowscn = GREATEST(D.rowscn, S.rowscn) 
WHEN NOT MATCHED THEN 
     INSERT (D.PRIMARY_KEY, D.UPDATE_DT, D.ROWSCN) 
     VALUES (S.PRIMARY_KEY, S.UPDATE_DT, S.ROWSCN); 

这个查询仍然给我 ORA-00904: invalid identifier 错误信息。

这是重现问题的 DDL。

CREATE TABLE MY_SOURCE_TABLE ( 
  PRIMARY_KEY NUMBER, 
  CREATED_DT TIMESTAMP(6), 
  UPDATED_DT TIMESTAMP(6) 
); 
 
CREATE TABLE MY_MERGE_TABLE ( 
  PRIMARY_KEY NUMBER, 
  UPDATED_DT TIMESTAMP(6), 
  ROWSCN NUMBER 
); 
 
INSERT INTO MY_SOURCE_TABLE (PRIMARY_KEY, CREATED_DT, UPDATED_DT) 
VALUES (1, SYSDATE-2, SYSDATE); 
 
INSERT INTO MY_SOURCE_TABLE (PRIMARY_KEY, CREATED_DT, UPDATED_DT) 
VALUES (2, SYSDATE-1, NULL); 
 
INSERT INTO MY_SOURCE_TABLE (PRIMARY_KEY, CREATED_DT, UPDATED_DT) 
VALUES (3, SYSDATE-1, SYSDATE+1); 
 
INSERT INTO MY_MERGE_TABLE (PRIMARY_KEY, UPDATED_DT, ROWSCN) 
VALUES (1, SYSDATE-2, 0); 
 
INSERT INTO MY_MERGE_TABLE (PRIMARY_KEY, UPDATED_DT, ROWSCN) 
VALUES (2, SYSDATE-1, 0); 

请您参考如下方法:

我运行了你的 SQL 并得到了同样的错误(在 11.2.0.1 上)。然后我尝试运行选择

SELECT PRIMARY_KEY,  
         COALESCE (UPDATE_DT, CREATED_DT) update_dt, 
         ORA_ROWSCN AS rowscn  
    FROM MY_SOURCE_TABLE; 

仍然出现 ORA-00904: "UPDATE_DT": invalid identifier 错误。然后我注意到在您的 DDL 中，MY_SOURCE_TABLE 上的列称为 UPDATED_DT(即带有额外的 D 字符)。在您的 MERGE 语句中更改对它的引用使它对我有用，希望对您有所帮助。