Data Warehouse job failing due to a custom lookup - how to identify it and resolve the problem

Blog Post created by Nika_Hadzhikidi Employee on Jun 1, 2017

Fellow Community members,


This is instructions to deal with a known issue when a custom dynamic lookup that is not having a DWH friendly query is enabled.
Data Warehouse is having strict constraints to ensure the data is always unique and the job will fail if any of the queries attempt to insert duplicates.


Error Message:

[CA Clarity][Oracle JDBC Driver][Oracle] ORA-01427: single-row subquery returns more than one row

1. Check the exact error message in Home - Job - Log or in bg-dwh.log to see which exact lookup fails.
2. Take the lookup table name, for example here is a part of an error: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_CUS_BUS_VAL_00 Lookup table is DWH_LKP_ CUS_BUS_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, example for CUS_BUS_VAL (note the second line from the bottom):


select object_name as Object_id, column_name as Attribute_code, lookup_type as Lookup_id
from ( select,,,   
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 'CUS_BUS_VAL%' )
group by object_name, column_name, lookup_type order by object_name, lookup_type

It has to include the % at the end of the lookup type name 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 it, Uncheck Include in Data Warehouse field, Save
6. Run Load Data Warehouse - Full Load and then Incremental Load. This should fix the issue for both Full and Incremental


Do not reenable the lookup back until the lookup query is corrected. Alternatively we suggest you modify the DWH lookup view and the DWH object view to make sure only unique records are returned. Once you do this, on Test environment and ensure it works you can promote the changes to Production.


Hope this helps -Nika