Adding a Static Lookup attribute to an object such as Department fails the Load Data Warehouse job with error message ORA-00904: "LAST_UPDATED_DATE": invalid identifier

book

Article ID: 4203

calendar_today

Updated On:

Products

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

Issue/Introduction

When adding a Static Lookup attribute to an object such as Department to be included in Data Warehouse it fails the Load Data Warehouse job with error message ORA-00904: "LAST_UPDATED_DATE": invalid identifier.

Steps to Reproduce:

  1. Go to Administration, Data Administration: Lookups 
  2. Create a new Static List lookup named 'Department Type' 
  3. Go to Values tab
  4. Add two values : Test1 and Test2 
  5. Save the lookup 
  6. Now to Administration, Studip: Objects, 'Department' 
  7. Go to Attributes tab
  8. Create a new attribute 'Department Type' based on the lookup we created above 
  9. Check the option 'Include in Data Warehouse' in the attribute properties, Save 
  10. Now run the job 'Load Data Warehouse' - Full Load

Expected Result: Load Data Warehouse Job to completed successfully. 
Actual Result: Load Data Warehouse fails with error ORA-00904: "LAST_UPDATED_DATE": invalid identifier on DWH_DEPARTMENT_V.

ERROR 2015-12-10 16:08:03,600 [DWH_DIM_FIELD_MAPPING - Table input 2] dwh.event Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException:
An error occurred executing SQL:
select (case
            when exists (SELECT 1
FROM DWH_DEPARTMENT_V src
WHERE
( LAST_UPDATED_DATE is null or LAST_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss') )
)
            then 1
            else 0
        end) as record_exists
from dual
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "LAST_UPDATED_DATE": invalid identifier  

Cause

Caused by CLRT-79485

Environment

This issue applies to CA PPM 14.2 and 14.3

Resolution

This issue has been fixed in CA PPM 14.4

Workaround:

 

Remove the custom attribute based on Static Lookup from the Department view.