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

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

Environment

Release : 15.8.1, 15.9

Component : CA PPM SAAS DATA WAREHOUSE

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.

 

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:PRTIMEENTRYID@,
       @SELECT:A.USER_VALUE:USERVALUE@,
       @SELECT:A.DISPLAY_VALUE:DISPLAYVALUE@,
       @SELECT:A.LANGUAGE_CODE:LANGUAGE_CODE@,
       @SELECT:A.LANGUAGE_ID:LANGUAGE_ID@,
       @SELECT:A.LAST_UPDATED_DATE:LAST_UPDATED_DATE@
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:USERDEFTSID_PARAM@
AND    LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
@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:USERDEFTSID_PARAM@
AND    TE.USER_LOV1 IS NULL
:BROWSE-ONLY@
) A
WHERE @FILTER@

 

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.