Parameterized Lookup query including a function causes Load Data Warehouse job to fail with ORA-00904: "SUBSTR": invalid identifier
Article ID: 103080
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaS - ApplicationClarity PPM On Premise
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.
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.
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
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.