[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3621
  • Last Modified:

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

getting ORA-30926 how can i fix it
thanks

MERGE INTO DMT_COMPENSATION_RATE_DM b
USING (
 SELECT EMPLID,
    EFFDT,
    EFFSEQ,
    ALLOWANCE_RATE_LOCAL,
    ALLOWANCE_RATE_USD_ACT,
    ALLOWANCE_RATE_USD_STD,
    ALLOWANCE_DESCRIPTION,
    BASE_RATE_LOCAL,
    BASE_RATE_USD_ACT,
    BASE_RATE_USD_STD,
    BASE_DESCRIPTION,
    CAR_RATE_LOCAL,
    CAR_RATE_USD_ACT,
    CAR_RATE_USD_STD,
    CAR_DESCRIPTION,
    CELL_RATE_LOCAL,
    CELL_RATE_USD_ACT,
    CELL_RATE_USD_STD,
    CELL_DESCRIPTION,
    CONTRACTOR_RATE_LOCAL,
    CONTRACTOR_RATE_USD_ACT,
    CONTRACTOR_RATE_USD_STD,
    CONTRACTOR_DESCRIPTION,
    HOURLY_RATE_LOCAL,
    HOURLY_RATE_USD_ACT,
    HOURLY_RATE_USD_STD,
    HOURLY_DESCRIPTION,
    incent_RATE_LOCAL,
    INCENT_RATE_USD_ACT,
    INCENT_RATE_USD_STD,
    INCENT_DESCRIPTION,
    PREM_RATE_LOCAL,
    PREM_RATE_USD_ACT,
    PREM_RATE_USD_STD,
    PREM_DESCRIPTION,
     SIS_RATE_LOCAL,
    SIS_RATE_USD_ACT,
    SIS_RATE_USD_STD,
    SIS_DESCRIPTION,
    WEEKLY_RATE_LOCAL,
    WEEKLY_RATE_USD_ACT,
    WEEKLY_RATE_USD_STD,
    WEEKLY_DESCRIPTION,
    FROM_DT,
    TO_DT,
    CURRENT_INDICATOR
FROM STG_COMPENSATION_RATE
) e
ON (b.EMPLID = e.EMPLID
and b.effdt = e.effdt)
WHEN MATCHED THEN
  UPDATE SET b.EFFSEQ=e.effseq,
     b.ALLOWANCE_RATE_LOCAL=e.ALLOWANCE_RATE_LOCAL,
    b.ALLOWANCE_RATE_USD_ACT= e.ALLOWANCE_RATE_USD_ACT,
    b.ALLOWANCE_RATE_USD_STD= e.ALLOWANCE_RATE_USD_STD,
    b.ALLOWANCE_DESCRIPTION =e.ALLOWANCE_DESCRIPTION,
    b.BASE_RATE_LOCAL= e.BASE_RATE_LOCAL,
    b.BASE_RATE_USD_ACT=e.BASE_RATE_USD_ACT,
    b.BASE_RATE_USD_STD=e.BASE_RATE_USD_STD,
    b.BASE_DESCRIPTION=e.BASE_DESCRIPTION,
    b.CAR_RATE_LOCAL=e.CAR_RATE_LOCAL,
    b.CAR_RATE_USD_ACT= e.CAR_RATE_USD_ACT,
    b.CAR_RATE_USD_STD=e.CAR_RATE_USD_STD,
    b.CAR_DESCRIPTION =e.CAR_DESCRIPTION,
    b.CELL_RATE_LOCAL=e.CELL_RATE_LOCAL,
    b.CELL_RATE_USD_ACT= e.CELL_RATE_USD_ACT,
    b.CELL_RATE_USD_STD= e.CELL_RATE_USD_STD,
    b.CELL_DESCRIPTION= e.CELL_DESCRIPTION,
    b.CONTRACTOR_RATE_LOCAL= e.CONTRACTOR_RATE_LOCAL,
    b.CONTRACTOR_RATE_USD_ACT= e.CONTRACTOR_RATE_USD_ACT,
    b.CONTRACTOR_RATE_USD_STD= e.CONTRACTOR_RATE_USD_STD,
    b.CONTRACTOR_DESCRIPTION = e.CONTRACTOR_DESCRIPTION,
    b.HOURLY_RATE_USD_ACT= e.HOURLY_RATE_USD_ACT,
    b.HOURLY_RATE_USD_STD= e.HOURLY_RATE_USD_STD,
    b.HOURLY_DESCRIPTION= e.HOURLY_DESCRIPTION,
    b.INCENT_RATE_LOCAL= e.INCENT_RATE_LOCAL,
    b.INCENT_RATE_USD_ACT= e.INCENT_RATE_USD_ACT,
    b.INCENT_RATE_USD_STD= e.INCENT_RATE_USD_STD,
    b.INCENT_DESCRIPTION= e.INCENT_DESCRIPTION,
    b.PREM_RATE_LOCAL=e.PREM_RATE_LOCAL,
    b.PREM_RATE_USD_ACT =e.PREM_RATE_USD_ACT,
    b.PREM_RATE_USD_STD= e.PREM_RATE_USD_STD,
    b.PREM_DESCRIPTION=e.PREM_DESCRIPTION,
    b.SIS_RATE_LOCAL= e.SIS_RATE_LOCAL,
    b.SIS_RATE_USD_ACT = e.SIS_RATE_USD_ACT,
    b.SIS_RATE_USD_STD= e.SIS_RATE_USD_STD,
    b.SIS_DESCRIPTION= e.SIS_DESCRIPTION,
    b.WEEKLY_RATE_LOCAL= e.WEEKLY_RATE_LOCAL,
    b.WEEKLY_RATE_USD_ACT= e.WEEKLY_RATE_USD_ACT,
    b.WEEKLY_RATE_USD_STD= e.WEEKLY_RATE_USD_STD,
    b.WEEKLY_DESCRIPTION= e.WEEKLY_DESCRIPTION,
    b.FROM_DT = e.FROM_DT,
    b.TO_DT= e.TO_DT,
    b.CURRENT_INDICATOR = e.CURRENT_INDICATOR
WHEN NOT MATCHED THEN
  INSERT(b.EMPLID,
  b.EFFDT,
    b.EFFSEQ,
    b.ALLOWANCE_RATE_LOCAL,
    b.ALLOWANCE_RATE_USD_ACT,
    b.ALLOWANCE_RATE_USD_STD,
    b.ALLOWANCE_DESCRIPTION,
    b.base_rate_local ,
    b.BASE_RATE_USD_ACT,
    b.BASE_RATE_USD_STD,
    b.BASE_DESCRIPTION,
    b.CAR_RATE_LOCAL,
    b.CAR_RATE_USD_ACT,
    b.CAR_RATE_USD_STD,
    b.CAR_DESCRIPTION,
    b.CELL_RATE_LOCAL,
    b.CELL_RATE_USD_ACT,
    b.CELL_RATE_USD_STD,
    b.CELL_DESCRIPTION,
    b.contractor_rate_local,
    b.CONTRACTOR_RATE_USD_ACT,
    b.CONTRACTOR_RATE_USD_STD,
    b.CONTRACTOR_DESCRIPTION,
    b.HOURLY_RATE_LOCAL,
    b.HOURLY_RATE_USD_ACT,
    b.HOURLY_RATE_USD_STD,
    b.HOURLY_DESCRIPTION,
    b.incent_rate_local,
    b.INCENT_RATE_USD_ACT,
    b.INCENT_RATE_USD_STD,
    b.INCENT_DESCRIPTION,
    b.PREM_RATE_LOCAL,
    b.PREM_RATE_USD_ACT,
    b.PREM_RATE_USD_STD,
    b.PREM_DESCRIPTION,
    b.sis_rate_local,
    b.SIS_RATE_USD_ACT,
    b.SIS_RATE_USD_STD,
    b.SIS_DESCRIPTION,
    b.WEEKLY_RATE_LOCAL,
    b.WEEKLY_RATE_USD_ACT,
    b.WEEKLY_RATE_USD_STD,
    b.WEEKLY_DESCRIPTION,
    b.FROM_DT,
    b.TO_DT,
    b.CURRENT_INDICATOR)
 values(e.EMPLID,
    e.EFFDT,
    e.EFFSEQ,
    e.ALLOWANCE_RATE_LOCAL,
    e.ALLOWANCE_RATE_USD_ACT,
    e.ALLOWANCE_RATE_USD_STD,
    e.ALLOWANCE_DESCRIPTION,
   cogSecure.encrypt(nvl(e.base_rate_local,0)) ,
    e.BASE_RATE_USD_ACT,
    e.BASE_RATE_USD_STD,
    e.BASE_DESCRIPTION,
    e.CAR_RATE_LOCAL,
    e.CAR_RATE_USD_ACT,
    e.CAR_RATE_USD_STD,
    e.CAR_DESCRIPTION,
    e.CELL_RATE_LOCAL,
    e.CELL_RATE_USD_ACT,
    e.CELL_RATE_USD_STD,
    e.CELL_DESCRIPTION,
    cogSecure.encrypt(nvl(e.contractor_rate_local,0)) ,
    e.CONTRACTOR_RATE_USD_ACT,
    e.CONTRACTOR_RATE_USD_STD,
    e.CONTRACTOR_DESCRIPTION,
    e.HOURLY_RATE_LOCAL,
    e.HOURLY_RATE_USD_ACT,
    e.HOURLY_RATE_USD_STD,
    e.HOURLY_DESCRIPTION,
    cogSecure.encrypt(nvl(e.incent_rate_local,0)) ,
    e.INCENT_RATE_USD_ACT,
    e.INCENT_RATE_USD_STD,
    e.INCENT_DESCRIPTION,
    e.PREM_RATE_LOCAL,
    e.PREM_RATE_USD_ACT,
    e.PREM_RATE_USD_STD,
    e.PREM_DESCRIPTION,
    cogSecure.encrypt(nvl(e.sis_rate_local,0)) ,
    e.SIS_RATE_USD_ACT,
    e.SIS_RATE_USD_STD,
    e.SIS_DESCRIPTION,
    e.WEEKLY_RATE_LOCAL,
    e.WEEKLY_RATE_USD_ACT,
    e.WEEKLY_RATE_USD_STD,
    e.WEEKLY_DESCRIPTION,
    e.FROM_DT,
    e.TO_DT,
    e.CURRENT_INDICATOR );
0
nav29
Asked:
nav29
1 Solution
 
Geraint_M_JonesCommented:
http://www.orafaq.com/forum/%22https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=96858.1"

Error: ORA-30926
Text:  Unable to get a stable set of rows in the source tables.
--------------------------------------------------------------------
Cause:  A stable set of rows could not be got because of large dml
        activity or a non-deterministic where clause.

Action: Remove any non-deterministic where clauses and reissue the dml.

"The MERGE statement works on the assumption that a 1:1 or a 1:0 relationship exists between the source table (or view) and the target table. This means that when you write the ON clause, the columns you specify there should be either the primary key or a unique key for both the source and the target table."
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now