Applying SQL functions on NSQL constructs, in a dynamic lookup query, results in no data for the corresponding attribute in DWH views
Article ID: 130884
Updated On:
Products
Clarity PPM SaaS
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.
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.
Environment
Clarity 15.6
Cause
This issue is caused due to a defect with ID DE48366.
Resolution
This is fixed in 15.6.1
Feedback
Yes
No
Powered by
