I included couple of Dynamic Lookups (Project Object Attributes) into Jaspersoft Datawarehouse. But after adding them my 'Load DataWarehouse' Job is failing.
Here the error that I am getting Error 7/28/16 7:14 AM User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_W_PROJECT_PURPOS_00 at Processor.processRow(Processor.java:65) at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181) at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60) at java.lang.Thread.run(Thread.java:745) Error 7/28/16 7:14 AM dwh_etl_interface_lkp - Errors detected! Error 7/28/16 7:14 AM ClarityDB - isOracle? - An error occurred executing this job entry : Couldn't execute SQL: INSERT INTO DWH_LKP_W_PROJECT_PURPOS_00(LANGUAGE_CODE, LANGUAGE_CODE_KEY, CLARITY_UPDATED_DATE, W_PROJECT_PURPOS_00_KEY, W_PROJECT_PURPOS_00, dw_updated_date) select LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, LOOKUP_CODE as W_PROJECT_PURPOS_00_KEY, NAME as W_PROJECT_PURPOS_00, to_date('2016/07/28 12:14:38', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date from DWH_LKP_W_PROJECT_PURPOS_00_V@PPMDBLINK where 1=1 and language_code in (select language_code from CMN_LANGUAGES@PPMDBLINK where is_dw_enabled=1) AND LAST_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss') [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "LANGUAGE_CODE": invalid identifier Error 7/28/16 7:14 AM User Defined Java Class - Unexpected error Error 7/28/16 7:14 AM ETL Job Failed. Please see log bg-dwh.log for details. Job Completed 7/28/16 7:14 AM NJS-0401: Execution of job failed.
And here is the query that I used in my Dynamic Lookup. I am able to include the attribute in DWH successfully but Job is failing. SELECT DISTINCT clv.lookup_code, clv.name, clv.sort_order, clv.last_updated_date, LANG.ID, LANG.LANGUAGE_CODE FROM cmn_lookups_v clv, CMN_LANGUAGES LANG LEFT OUTER JOIN cmn_lookups_v clv1 ON clv1.lookup_code=@WHERE:PARAM:USER_DEF:STRING:W_ACTIVITY_TYPE@ WHERE @FILTER@ AND clv.lookup_type = 'W_PROJECT_PURPOSE_LIST' AND clv.language_code = 'en' AND LANG.LANGUAGE_CODE=clv.LANGUAGE_CODE AND ( ( clv1.lookup_code IN ('ACTIVITY_TYPE_INVEST') AND clv.lookup_code IN ('PRJ_PUR_REGULATORY_LEGAL','PRJ_PUR_SECURITY_RISK','PRJ_PUR_REFRESH_REPLACE', 'PRJ_PUR_ ADDITIONAL_TECH_CAPAC','PRJ_PUR_NEW_FOUNDATIONAL_CAPAB','PRJ_PUR_NEW_BUSINESS_CAPABILIT') ) )
Thank you for raising this : since you also have a Support issue on this, I will work with you on it and we will then update the post with the resolution.
Hi Nika_Hadzhikidi - Any resolution for this one yet? Thanks! Chris
I tested the lookup query which Prasanth provided and was able to successfully add it to Data Warehouse on the same PPM release. This would mean that the lookup query was not actually what caused the issue.
I advised Prasanth the NSQL query is correct, and to try recreating the lookup with the same query and see if this lookup gets added correctly.
It seems like the issue was now resolved, I am yet pending Prasanth to confirm what was the resolution.
When adding dynamic lookups be sure to reference the guides. Specific attributes need to be included Perhaps, created date and the like. It might be related. Support will be able to tell exactly.
I also agree with Austin. These issues usually occur when you add a Dynamic lookup attribute in the datawarehouse. I am sure our engineer from CA Support will help you further on that.
Prasanth managed to resolve the issue by changing the query to include:
in uppercase. In his lower environment, the query worked with no issue, but in Production the uppercase was required to make the Load Data Warehouse complete.
Thank you -Nika