When using a Parameterized lookup, and including the attribute in Data Warehouse (DWH), the Load Data Warehouse job - Full Load runs well, and then the 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
The error may vary for different database vendors
Release: All Supported
Component: Clarity Data Warehouse (DWH)
This error occurs where there are 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 cause:
You see that the field selected is not having unique entries in the database table, allowing for duplicates. This is not recommended and the Data Warehouse will not work with duplicates as it has enforced constraints to ensure data quality.
This is not a defect - DWH is enforcing unique entries by design