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

Document ID : KB000004203
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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  

Environment:
This issue applies to CA PPM 14.2 and 14.3
Cause:

Caused by CLRT-79485

Resolution:

This issue has been fixed in CA PPM 14.4

Workaround:

 

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