Load Data Warehouse fails when Custom Lookup is used for User Lookup Value 1 in Postgres (GCP) - column src.language_code does not exist

Products

• Clarity PPM SaaS
• Clarity PPM On Premise
• STARTER PACK-CLARITY PPM

Issue/Introduction

 

We included a new User Value 1 lookup on the Timesheet Options page, and saved. Ever since then the Load Data Warehouse job fails with error: 

Error message (lookup name may vary):

 An error occurred executing this job entry :

Couldn't execute SQL: DELETE FROM DWH_LKP_TIME_ENTRY_USER__00
WHERE NOT (EXISTS (SELECT 1
                                                FROM   DWH_LKP_TIME_ENTRY_USER__00_V SRC
                                                WHERE  SRC.PRTIMEENTRYID = DWH_LKP_TIME_ENTRY_USER__00.TIME_ENTRY_USER__00_KEY
                                                          AND    SRC.LANGUAGE_CODE = DWH_LKP_TIME_ENTRY_USER__00.LANGUAGE_CODE))
ERROR: column src.language_code does not exist
  Position: 227

Cause

The lookup selected was not created by Load Data Warehouse best practices and does not contain language code (it looks for it as per the error).

Note:  the lookup selected also MUST be having PRTIMEENTRY.PRID as hidden key.

 

Environment

Release : 15.8.1, 15.9

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

Logged as DE57004, Not a Bug

Get the job to run successfully:

For the time being we recommend removing the User Value lookup from Timesheet Options (set back to the default lookup) and running a Full Load to get the job to run. Once the job is stabilized, please work with your developer team to correct the Timeentry User Value 1 lookup query.

1. Go to Administration - Timesheet Options
2. Remove the lookup Time Entry User Value 1 there and replace with Time Entry User Lookup field 1 (this is the default value)
3. Save
4. Run Load Data Warehouse - Full Load

Fix the custom lookup query if you still want to use it, before enabling it again:

If at some point you want still to use a custom lookup, please modify the custom dynamic query for Time Entry User Value 1 to include LANGUAGE_ID as in the tips below:

Use a lookup query to similar to this (must include LANGUAGE_CODE and LANGUAGE_ID)

SELECT @SELECT:A.PRTIMEENTRYID:[email protected],
       @SELECT:A.USER_VALUE:[email protected],
       @SELECT:A.DISPLAY_VALUE:[email protected],
       @SELECT:A.LANGUAGE_CODE:[email protected],
       @SELECT:A.LANGUAGE_ID:[email protected],
       @SELECT:A.LAST_UPDATED_DATE:[email protected]
FROM  
(SELECT TE.PRID PRTIMEENTRYID ,
       SUBSTR(OT.CODE,1,30) USER_VALUE ,
       SUBSTR(OT.CODE,1,30) DISPLAY_VALUE ,
       LANG.LANGUAGE_CODE LANGUAGE_CODE ,
       LANG.ID LANGUAGE_ID ,
       TE.PRMODTIME LAST_UPDATED_DATE
FROM   PRTIMESHEET TS
       INNER JOIN PRTIMEENTRY TE ON TS.PRID = TE.PRTIMESHEETID
       INNER JOIN CMN_LANGUAGES LANG ON LANG.IS_DW_ENABLED = 1
       JOIN ODF_CA_TESTING_TIME OT ON OT.CODE = TE.USER_LOV1
WHERE  TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
AND    LANG.LANGUAGE_CODE = @WHERE:PARAM:[email protected]
@BROWSE-ONLY:
UNION  
SELECT TE.PRID PRTIMEENTRYID ,
       SUBSTR(OT.CODE,1,30) USER_VALUE ,
       SUBSTR(OT.CODE,1,30) DISPLAY_VALUE ,
       'en' LANGUAGE_CODE ,
       1 LANGUAGE_ID ,
       TE.PRMODTIME LAST_UPDATED_DATE
FROM   PRTIMESHEET TS
       INNER JOIN PRTIMEENTRY TE ON TS.PRID = TE.PRTIMESHEETID
       LEFT JOIN ODF_CA_TESTING_TIME OT ON 1=1
WHERE  TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
AND    TE.USER_LOV1 IS NULL
:[email protected]
) A
WHERE @[email protected]

 

Here are some of the best practices:

https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=745558#bmcdc4e66d-b180-43f9-bc83-65baecd9b54c

We also recommend all changes like this to be done on a lower environment first, ensure the job is running well, and then you can repeat the same in Production.