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

book

Article ID: 130884

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

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:[email protected],
@select:full_name:[email protected],
@select:last_updated_date:[email protected]
from srm_resources sr
where @[email protected]
and unique_name = lower(@where:param:user_def:string:[email protected])


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.

Environment

Release: CODSFF99100-15.6-PPM SAAS-Full Function User
Component:

Resolution

DE48366 is being reviewed by Product Engineering for possible fixes.