CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10gsql-merge
karthik
karthik

Reputation: 263

ORA-30926: unable to get a stable set of rows in the source tables when running Merge Query

enter image description here

I am executing a merge query to update 2 columns in a table, but I get the following error "ORA-30926: unable to get a stable set of rows in the source tables.

When I execute the merge query but I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but Oracle still throws the error.

What can I do to resolve this?

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY,
      AA_PERSON_ASSIGNMENT_KEY,
      SCHEDULE_LINE_ID,
      SRC_CREATED_DATE,
      SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE
  WHERE
    A.SRC_CREATED_DATE      <> B.SRC_CREATED_DATE
  OR A.SRC_LAST_UPDATE_DATE <> B.SRC_LAST_UPDATE_DATE;
   COMMIT;

Upvotes: 1

Views: 389

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but Oracle still throws the error.

Your

ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn

with a filter rn=1 removes duplicates by 6 columns, while you are using 4 columns in ON() clause. Moreover you are using another filter to filter rows for update.

The easiest way to get what you want is to filter needed data in USING clause:

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      BB.PAY_RANGE_START_DATE_KEY,
      BB.AA_PERSON_NATURAL_KEY,
      BB.AA_PERSON_ASSIGNMENT_KEY,
      BB.SCHEDULE_LINE_ID,
      BB.SRC_CREATED_DATE,
      BB.SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( 
         PARTITION BY BB.PAY_RANGE_START_DATE_KEY,
                      BB.AA_PERSON_NATURAL_KEY, 
                      BB.AA_PERSON_ASSIGNMENT_KEY, 
                      BB.SCHEDULE_LINE_ID
         ORDER BY ROWNUM /*?*/ 
         ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356 BB
     ,EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP AA
    WHERE
      AA.PAY_RANGE_START_DATE_KEY = BB.PAY_RANGE_START_DATE_KEY AND
      AA.AA_PERSON_NATURAL_KEY    = BB.AA_PERSON_NATURAL_KEY AND
      AA.AA_PERSON_ASSIGNMENT_KEY = BB.AA_PERSON_ASSIGNMENT_KEY AND
      AA.SCHEDULE_LINE_ID         = BB.SCHEDULE_LINE_ID
      AND (
          AA.SRC_CREATED_DATE     <> BB.SRC_CREATED_DATE
       OR AA.SRC_LAST_UPDATE_DATE <> BB.SRC_LAST_UPDATE_DATE
      )
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE;

As you can see I filtered rows you don't need in the USING clause, so you don't need WHERE clause in UPDATE SET and removed duplicates by 4 columns used for matching

Upvotes: 2

Related Questions