Load DWH job failure on Incremental ORA-30926: unable to get a stable set of rows in the source tables
Article ID: 103891
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaS - ApplicationClarity PPM On Premise
When using a Parameterized lookup, and including the attribute in Data Warehouse, the Load Data Warehouse job - Full Load runs well, and then Incremental fails with error: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_TEST_00). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at line 22
This error occurs where there is duplicates in the lookup tables. For parameterized lookups it's usually caused by a wrong hidden key that does not use a unique key for a join.
To confirm this is the case do the following:
Go to Administration - Lookups - open the lookup - Query - note the table we're selecting from.
Now go to Administration - Lookups - open the lookup - Parent Window - Hidden key
Check the hidden key column value in the table from step 1.
We see that the field selected is not having unique entries in the database table, allowing for duplicates. This is not recommended and Data Warehouse will not work with duplicates as it has enforced constraints to ensure for data quality.
Best practice when creating parameterized lookups is to always pick a hidden key that is a unique value. In most cases, we select the ID in the custom object table. So for this case it would be ODF_CA_TEST.ID. This would ensure there is no way any duplicate would occur when joining the two tables and Load Data Warehouse will always run well.
If for some reason this is not possible, uncheck the fields from DWH for until you can correct the lookups.
Last possibility is to uncheck the lookups from DWH, then create two Calculated fields (strings) referring to the same values, and include those in DWH instead.