Oracle Merge Statement Behavior when ora_rowscn is in USING clause

Question

I have a MERGE statement that's giving me the dreaded ORA-00904: invalid identifier error message. Note that the typical issues with the "invalid identifier" error are not present here - I'm not trying to update the joined column, nor have I misspelled my column names. I am attempting to use the oracle pseudo-column ORA_ROWSCN in the USING clause's SELECT statement.

With these sample tables, I try to run

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);

If I remove the pseudocolumn ora_rowscn from the USING clause, I no longer receive the error message, and the merge completes successfully.

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);

If I instead place the query into a VIEW, I can then use the ora_rowscn successfully:

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);

Is there any way to do this without creating a VIEW for the query? I have to do this on many tables as part of an ETL process, and would prefer not to have to build several views.

EDIT: Based on Glenn's suggestion in the comments, I tried putting the query into a subquery:

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);

This query still gives me the ORA-00904: invalid identifier error message.

Here's DDL to recreate the issue.

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);

The merge statement works fine for me on 11.2.0.1. You may need to post the DDL and some sample data. — Jon Heller, Feb 18 '12 at 04:54
Can you use subquery factoring in the "using" select clause as in the accepted answer of this question: http://stackoverflow.com/questions/6673806/oracle-with-clause-merge-syntax-error/6673940#6673940 — Glenn, Feb 21 '12 at 16:06
@jonearles - Does the merge statement work for you when you are using the pseudocolumn ORA_ROWSCN in your SELECT statement as part of the USING clause? I use merge all the time without it and it's fine. It's this specific case that I'm having problems with. — N West, Feb 23 '12 at 14:58
@Glenn - I tried wrapping the statement in a WITH clause and also just a subquery, it still gives the Invalid Identifier error message. I will update the question to reflect that I tried that. — N West, Feb 23 '12 at 14:58
I created tables with the same names and columns and ran the exact same SQL statement without error. — Jon Heller, Feb 23 '12 at 17:42
@jonearles please look at my DDL I just added. I am trying to use the ORA_ROWSCN pseudocolumn which is the internal oracle system change number. — N West, Feb 23 '12 at 22:43

score 5 · Accepted Answer · edited Feb 27 '12 at 20:36

I ran your SQL and got the same error (on 11.2.0.1). Then I tried to run the select

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

Still got the ORA-00904: "UPDATE_DT": invalid identifier error. Then I noticed that in your DDL, the column on MY_SOURCE_TABLE is called UPDATED_DT (i.e. with an extra D character). Changing the references to that in your MERGE statement made it work for me, hope that helps.

Oracle Merge Statement Behavior when ora_rowscn is in USING clause

1 Answers1