Data Provider built in query missing join to LN table on sub-object.

Products

• Clarity PPM On Premise

Issue/Introduction

We setup a Reporting Data provider to have a custom master object and one custom subobject of that master.

 

When you try to use a lookup field in preview that is tied to the subobject you get an invalid identifier error and it fails. In the log you will find the query the designer built and it doesn't include a join to the subobject DW LN table to get the value for the lookup. It does have a join to the master object DW LN table and those fields work fine in preview. Any field you add that is a lookup field from the subobject fails because the query being built doesn't have a join to the LN table for the subobject. The select for the field from the LN table is there but not the join to reference the table.

 

Steps to Reproduce:

 

1. Create master object.

2. Create sub object of that master object.

3. Create dynamic lookup with sample NSQL below:

SELECT @SELECT:u.id:user_id@,

    @SELECT:u.USER_NAME:UNIQUE_CODE@,

    @SELECT:r.id:resource_id@,

    @SELECT:r.unique_name:unique_name@,

    @SELECT:r.first_name:first_name@,

    @SELECT:r.last_name:last_name@,

    @SELECT:r.person_type:person_type_id@,

    @SELECT:r.full_name:full_name@,

    @SELECT:l.name:person_type@,

@SELECT:LANG.ID:LANGUAGE_ID@,

@SELECT:l.LANGUAGE_CODE:LANGUAGE_CODE@,

@SELECT:r.LAST_UPDATED_DATE:LAST_UPDATED_DATE@

FROM  srm_resources r,

    cmn_sec_users u,

    cmn_lookups_v l,

    cmn_lookups_v s,

CMN_LANGUAGES LANG

WHERE  u.id = r.user_id

AND   r.IS_HIDDEN = 0

AND   @FILTER@

AND   r.person_type = l.id

AND   l.language_code = @WHERE:PARAM:LANGUAGE@

AND   l.lookup_type = 'SRM_RESOURCE_TYPE'

AND   u.user_status_id = s.id

AND   s.LANGUAGE_CODE = LANG.LANGUAGE_CODE

AND   s.language_code = @WHERE:PARAM:LANGUAGE@

AND   s.lookup_type = 'SEC_USER_STATUS'

AND   s.lookup_code IN ('ACTIVE','LOCK')

 

4. Create lookup type attribute on the custom sub object pointing to the lookup created in the above step.

5. Ensure both objects as well as the attribute created above are selected to be included in the Data Warehouse.

6. Create few instances of subobject as well as subobject and populate lookup attribute on the subobject with some sample values.

7. Navigate to Reports and Job and run Load Data Warehouse with Full Load.

8. Navigate to MUX->Reporting->Data Providers.

9. Create new Data provider and select both master and subobject from Available to Selected section.

10. Update Join type to Left.

11. Go to Preview tab and drop some sample columns along with the lookup attribute.

12. Notice errors come up and no records are showing.

 

Expected Results: It should show data.

 

Actual Results: It throws an error and no data is displayed.

Environment

Clarity 16.3.3

Cause

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "DWH_ODF_CUST_SUB_LN"."C_TEST": invalid identifier

DE176405

Resolution

Unable to reproduce in 16.4.0.