Parameterized Lookup query including a function causes Load Data Warehouse job to fail with ORA-00904: "SUBSTR": invalid identifier

Document ID : KB000103080
Last Modified Date : 22/06/2018
Show Technical Document Details
Issue:
When we include a custom parameterized dynamic lookup query with a function in Data Warehouse, Data Warehouse job will fail with error similar to this:
 
 
WARN 2018-04-17 10:22:20,495 [http-nio-80-exec-12] odf.view-generation (clarity:admin:5154040__DD7E9BEF-5A12-4138-AD1D-3E11CDF29984:odf.updateObjectDefinitionAttribute) A SQL exception occured when creating view DWH_DW_INVESTMENT_V. The view will not be recreated. [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "SUBSTR": invalid identifier
 
WARN 2018-04-05 23:11:44,879 [http-nio-80-exec-291] odf.view-generation (clarity:admin:40026208__28902728-8DD6-4F49-BCC1-75D25F4BB0B9:odf.updateObjectDefinitionAttribute) A SQL exception occured when creating view DWH_DW_INVESTMENT_V. The view will not be recreated. [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "REGEXP_SUBSTR": invalid identifier

This can happen on any function, not only SUBSTR but REGEXP_SUBSTR etc.
Cause:
DE40520 Parameterized Lookup query including a function does not evaluate correctly to create the DWH View and fails Load Data Warehouse job (Oracle)
 
The code expects the evaluation to be flipped around.
 
Resolution:
Workaround:
 
1. Update the lookup breakdwh from query (note the last line):
select @select:1:id@,@select:'test':code@,@select:'a test value':name@
from dual
where @filter@
and (@where:param:user_def:string:breakdwh@ is null
or @where:param:user_def:string:breakdwh@ = substr('breakdwh', 1, 10))
 
 
To query:
 
select @select:1:id@,@select:'test':code@,@select:'a test value':name@
from dual
where @filter@
and (@where:param:user_def:string:fixdwh@ is null
or substr('fixdwh', 1, 10) = @where:param:user_def:string:fixdwh@)
Basically you flip the two conditions around.
 
You can update the lookup query by XOG, it will NOT work to update from PPM UI.
 
 
2. Restart the services
3. Now ensure the lookup query is showing the new query in Administration - Lookups
4. Go to Investment object - Attributes
5. Enable the attribute for Data Warehouse
6. Run Load Data Warehouse job - Full
 
Additional Information:
Please note all parameterized lookups with a function included may hit this problem, so before enabling them for Data Warehouse we strongly recommend reviewing the lookup query. You can check whether you're hitting the bug by enabling the attribute for Data Warehouse and checking the app-ca log for ORA-00904: invalid identifier on the function, without running the job.
If you have this error you will have to flip the conditions as per above.