sql - update - Oracle Merge Statement Behavior cuando ora_rowscn está en la cláusula USING

sentencia merge sql

Question

Tengo una declaración MERGE que me está dando el temido ORA-00904: invalid identifier mensaje de error ORA-00904: invalid identifier . Tenga en cuenta que los problemas típicos con el error del "identificador no válido" no están presentes aquí. No estoy intentando actualizar la columna unida ni he escrito mal mis nombres de columna. Estoy intentando utilizar la pseudocolumna de ORA_ROWSCN en la instrucción SELECT la cláusula USING .

Con estas tablas de muestra, trato de correr

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

Si elimino la pseudocolumna ora_rowscn de la cláusula USING, ya no recibiré el mensaje de error y la fusión se completará correctamente.

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

Si en cambio coloco la consulta en una VISTA, puedo usar ora_rowscn con éxito:

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

¿Hay alguna manera de hacer esto sin crear una VISTA para la consulta? Tengo que hacer esto en muchas tablas como parte de un proceso de ETL, y preferiría no tener que construir varias vistas.

EDITAR: Basado en la sugerencia de Glenn en los comentarios, traté de poner la consulta en una subconsulta:

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

Esta consulta todavía me proporciona el ORA-00904: invalid identifier mensaje de error de ORA-00904: invalid identifier .

Aquí está DDL para recrear el problema.

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

Answer 1

Ejecuté su SQL y obtuve el mismo error (en 11.2.0.1). Entonces intenté ejecutar el

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

Todavía obtuvo el ORA-00904: "UPDATE_DT": invalid identifier error de ORA-00904: "UPDATE_DT": invalid identifier . Luego noté que en su DDL, la columna en MY_SOURCE_TABLE se llama UPDATED_DT (es decir, con un carácter D adicional). Cambiar las referencias a eso en su declaración MERGE hizo que funcionase para mí, espero que eso ayude.