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

book

Article ID: 103080

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

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.
 

Environment

Release:
Component: ODDWH

Resolution

Workaround:
 
1. Update the lookup breakdwh from query (note the last line):
select @select:1:[email protected],@select:'test':[email protected],@select:'a test value':[email protected]
from dual
where @[email protected]
and (@where:param:user_def:string:[email protected] is null
or @where:param:user_def:string:[email protected] = substr('breakdwh', 1, 10))
 
 
To query:
 
select @select:1:[email protected],@select:'test':[email protected],@select:'a test value':[email protected]
from dual
where @[email protected]
and (@where:param:user_def:string:[email protected] is null
or substr('fixdwh', 1, 10) = @where:param:user_def:string:[email protected])
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.