Applying SQL functions on NSQL constructs, in a dynamic lookup query, results in no data for the corresponding attribute in DWH views

Document ID : KB000130884
Last Modified Date : 15/04/2019
Show Technical Document Details
Issue:
There is a problem with the transformation that happens for Dynamic Lookups, that contain a SQL function wrapping an NSQL construct, when they are included to be loaded into the DWH. It has been noticed that the part that fetches the "ID" of the values works fine, but the part that fetches the "Name", is incorrectly getting tranformed in such a manner that it fetches only NULL values. The steps below show how this issue can be determined in the PPM schema itself without having to run the Load Data Warehouse job. Upon running the Load Data Warehouse job, the data from the view mentioned below, gets copied into the Project tables of DWH.

Steps To Reproduce:
1. Create a dynamic lookup query with the following code:
select
@select:id:id@,
@select:full_name:full_name@,
@select:last_updated_date:last_updated_date@
from srm_resources sr
where @filter@
and unique_name = lower(@where:param:user_def:string:unique_name@)


2. Create an attribute in the Project object and link it to the lookup created in step 1
3. Map this attribute to the Name attribute in the Project object
4. Make this attribute available for data entry by displaying it in the Project properties page
5. Enter data against this attribute for a few projects (Note: Change the Project name to that of a Resource ID, for data to get populated in the attribute created in step 2)

6. Mark the attribute created in step 2 for DWH
7. Query the dwh_project_v view in the PPM schema. There should be two columns created newly in it _key and _caption

Expected Results: Both columns _key and _caption have values against them for projects used in step 5

Actual Results: Only _key has values, and _caption has only null values in it.
Cause:
This issue is caused due to a defect with ID DE48366.
Resolution:
DE48366 is being reviewed by Product Engineering for possible fixes.