Postgres/GCP/SaaS Load DWH Job Fails When Using Lookup With Dynamic Query Referencing Clarity Function

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The dynamic query associated with a custom lookup 
 is not able to reference the tables in the function, e.g. nbi_get_obs_flat_path_fct

Steps To Reproduce

1. Create lookup EXAMPLE_OBS with following dynamic query: 

SELECT
@SELECT:id:id@,
@SELECT:depth:depth@,
@SELECT:OBS_U.obs:obs@,
@SELECT:CMN_CURRENTDATE_FCT():last_updated_date@
FROM (
   SELECT 
   id,
   CASE 
     WHEN OBS_U.id = null 
      THEN ''
      ELSE '/'
     END
     || enterschema.nbi_get_obs_flat_path_fct(OBS_U.id)    AS obs,
   DEPTH
   FROM enterschema.prj_obs_units OBS_U
   WHERE 5=5   
    AND DEPTH IN (1,2)
     ) OBS_U
WHERE 1=1
AND @WHERE:SECURITY:PROJECT:INV_INVESTMENTS.ID@
AND @FILTER@

2. Create project object attribute with lookup created above.

3. Run the full Load DWH job.

Expected: The job should complete.

Actual: The job fails with following error/excerpt in the BG DWH logs:

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_EXAMPLE_OBS. relation "prj_obs_units_flat" does not exist
  Where: PL/pgSQL function inline_code_block line 20 at RAISE

Resolution

The dynamic query is not able to reference the tables in the function: nbi_get_obs_flat_path_fct

Use the following query instead:

select 
@select:id:id@,
@select:depth:depth@,
@select:obs_u.obs:obs@,
@select:cmn_currentdate_fct():last_updated_date@
from 
(select OBS_U.id, case WHEN OBS_U.id = null THEN '' ELSE '/' end || nbi_get_obs_flat_path_fct(OBS_U.id) as obs, depth 
from prj_obs_units OBS_U
where depth in (1,2)
) obs_u
where 1=1
AND @WHERE:SECURITY:PROJECT:INV_INVESTMENTS.ID@
AND @FILTER@