Your Load Data Warehouse (DWH) job is failing on a custom lookup, could be with one of the errors below:
ORA-00904: invalid identifier ORA-30926: unable to get a stable set of rows in the source tables ORA-01427: single-row subquery returns more than one row
The lookup might not be having a proper query to work with DWH, how can I find out which attribute and object it is on, to disable the lookup from DWH?
Release: All Supported Releases
Component: Clarity Data Warehouse
How to find out the attribute / object affected for the custom lookup:
ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_TEST_VAL_00
Lookup table is DWH_LKP_ TEST_VAL _00
3. Remove the DWH_LKP_ and the _00 if present. You need the lookup name only.
4. Modify the query below to include the corrected value
select object_name as Object_id, column_name as Attribute_code, lookup_type as Lookup_id
from (
select clt.id, clov.id, oca.id,
clt.lookup_type, clt.source,
clov.sql_text_id, clov.object_code,
oca.object_name, oca.internal_name, oca.column_name, oca.data_type,
oca.partition_code, oca.is_multivalued, oca.default_value, oca.derived_object_code,oca.internal_name
from cmn_lookup_types clt
join cmn_list_of_values clov
on clt.lookup_type = clov.lookup_type_code
and clov.is_system = 0
join odf_custom_attributes oca
on oca.lookup_type = clt.lookup_type
and clt.lookup_type like '%TEST_VAL%'
)
group by object_name, column_name, lookup_type
order by object_name, lookup_type
Note: It has to include the % at the end as sometimes not the full lookup name is used in the table name.
5.The query will give you the exact object name and attribute that has to be unchecked from Data Warehouse. Connect to Administration - Objects - <object> - Attributes - Select and Uncheck it.
6. Run Load Data Warehouse - Full Load
7. Then run Load DWH - Incremental Load
This should fix the issue.