Поведение оператора слияния Oracle, когда ora_rowscn находится в предложении USING

Question

У меня есть заявление MERGE, которое дает мне страшные ORA-00904: invalid identifier сообщение об ошибке. Обратите внимание, что типичные проблемы с ошибкой "неверный идентификатор" здесь не присутствуют - я не пытаюсь обновить объединенный столбец и не ошибся в именах своих столбцов. Я пытаюсь использовать оракул псевдоколонку ORA_ROWSCN в USING статья-х SELECT заявление.

С этими примерами таблиц я пытаюсь запустить

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

Если я удаляю псевдостолбец ora_rowscn из предложения USING, я больше не получаю сообщение об ошибке, и слияние завершается успешно.

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 и предпочел бы не создавать несколько представлений.

РЕДАКТИРОВАТЬ: Основываясь на предложении Гленна в комментариях, я попытался поместить запрос в подзапрос:

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

Answer 1

Я запустил ваш 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 Заявление сделало это работает для меня, надеюсь, что помогает.