tSQLTemplateMerge - Query statement generation error with Oracle 11g
Hi,
I am trying to use the tSQLTemplateMerge component to replace our current ETL PL/SQL statement. But I can not get it to work since it generates a wrong statement to the Oracle database. All the information is below.
Thanks in advance for your help.
Francois
Exception in component tSQLTemplateMerge_1
java.sql.BatchUpdateException: error occurred during batching: ORA-00971: missing SET keyword
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:687)
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:725)
at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:4113)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:211)
at hses_etl.empty_job_0_1.EMPTY_JOB.tSQLTemplateMerge_1Process(EMPTY_JOB.java:949)
at hses_etl.empty_job_0_1.EMPTY_JOB.tOracleConnection_1Process(EMPTY_JOB.java:754)
at hses_etl.empty_job_0_1.EMPTY_JOB.runJobInTOS(EMPTY_JOB.java:3792)
at hses_etl.empty_job_0_1.EMPTY_JOB.main(EMPTY_JOB.java:3535)
After debugging, I could locate part of the the merge SQL statement (mergeStringUpdate variable) that causes trouble. You can not have two tables in the update statement. Here it is :
Error starting at line 1 in command:
UPDATE FACT_PROGRAM_SUMMARY,
FACT_PROGRAM_SUMMARY_MERGE
SET FACT_PROGRAM_SUMMARY.GRANTEE_ID = FACT_PROGRAM_SUMMARY_MERGE.GRANTEE_ID,
FACT_PROGRAM_SUMMARY.REGIONAL_MANAGER_ID = FACT_PROGRAM_SUMMARY_MERGE.REGIONAL_MANAGER_ID,
FACT_PROGRAM_SUMMARY.GRANT_SPECIALIST_ID = FACT_PROGRAM_SUMMARY_MERGE.GRANT_SPECIALIST_ID,
FACT_PROGRAM_SUMMARY.REGIONAL_MANAGER_ID = FACT_PROGRAM_SUMMARY_MERGE.PROGRAM_SPECIALIST_ID,
FACT_PROGRAM_SUMMARY.TA_SPECIALIST_ID = FACT_PROGRAM_SUMMARY_MERGE.TA_SPECIALIST_ID,
FACT_PROGRAM_SUMMARY.TA_MANAGER_ID = FACT_PROGRAM_SUMMARY_MERGE.TA_MANAGER_ID,
FACT_PROGRAM_SUMMARY.PROGRAM_COUNT = FACT_PROGRAM_SUMMARY_MERGE.PROGRAM_COUNT
WHERE (FACT_PROGRAM_SUMMARY.GRANT_ID = FACT_PROGRAM_SUMMARY_MERGE.GRANT_ID
AND FACT_PROGRAM_SUMMARY.PROGRAM_ID = FACT_PROGRAM_SUMMARY_MERGE.PROGRAM_ID)
Error at Command Line:1 Column:27
Error report:
SQL Error: ORA-00971: missing SET keyword
00971. 00000 - "missing SET keyword"
*Cause:
*Action:
The full correct MERGE SQL Statement that works with Oracle is - I was expecting to find somehow this generated by Talend component but could not find it while debugging:
MERGE INTO OHS_DATA_MART.FACT_PROGRAM_SUMMARY T
USING (SELECT (SELECT GRANT_ID
FROM OHS_DATA_MART.DIM_GRANT
WHERE GRANT_NUMBER = S.GRANT_NUMBER) AS GRANT_ID
,(SELECT GRANTEE_ID
FROM OHS_DATA_MART.DIM_GRANTEE
WHERE ORGANIZATION_ID = S.ORGANIZATION_ID) AS GRANTEE_ID
,(SELECT MAX(PROGRAM_ID)
FROM OHS_DATA_MART.DIM_PROGRAM
WHERE SYS_HS_PROGRAM_ID = S.SYS_HS_PROGRAM_ID
AND RECORD_CURRENT = 'Yes') AS PROGRAM_ID
,NVL((SELECT REGIONAL_MANAGER_ID
FROM OHS_DATA_MART.DIM_REGIONAL_MANAGER
WHERE REGIONAL_MANAGER_FULL_NAME = S.REGIONAL_MANAGER), 0) AS REGIONAL_MANAGER_ID
,NVL((SELECT GRANT_SPECIALIST_ID
FROM OHS_DATA_MART.DIM_GRANT_SPECIALIST
WHERE GRANT_SPECIALIST_FULL_NAME = S.GRANT_SPECIALIST), 0) AS GRANT_SPECIALIST_ID
,NVL((SELECT PROGRAM_SPECIALIST_ID
FROM OHS_DATA_MART.DIM_PROGRAM_SPECIALIST
WHERE PROGRAM_SPECIALIST_FULL_NAME = S.PROGRAM_SPECIALIST), 0) AS PROGRAM_SPECIALIST_ID
,NVL((SELECT TA_SPECIALIST_ID
FROM OHS_DATA_MART.DIM_TA_SPECIALIST
WHERE TA_SPECIALIST_FULL_NAME = S.TA_SPECIALIST), 0) AS TA_SPECIALIST_ID
,NVL((SELECT TA_MANAGER_ID
FROM OHS_DATA_MART.DIM_TA_MANAGER
WHERE TA_MANAGER_FULL_NAME = S.TA_MANAGER), 0) AS TA_MANAGER_ID
,S.PROGRAM_COUNT
FROM OHS_ETL.FACT_PROGRAM_SUMMARY S) S
ON ( T.GRANT_ID = S.GRANT_ID
AND T.PROGRAM_ID = S.PROGRAM_ID)
WHEN MATCHED THEN
UPDATE SET T.GRANTEE_ID = S.GRANTEE_ID
,T.REGIONAL_MANAGER_ID = S.REGIONAL_MANAGER_ID
,T.GRANT_SPECIALIST_ID = S.GRANT_SPECIALIST_ID
,T.PROGRAM_SPECIALIST_ID = S.PROGRAM_SPECIALIST_ID
,T.TA_SPECIALIST_ID = S.TA_SPECIALIST_ID
,T.TA_MANAGER_ID = S.TA_MANAGER_ID
,T.PROGRAM_COUNT = S.PROGRAM_COUNT
WHEN NOT MATCHED THEN
INSERT
(GRANT_ID
,GRANTEE_ID
,PROGRAM_ID
,REGIONAL_MANAGER_ID
,GRANT_SPECIALIST_ID
,PROGRAM_SPECIALIST_ID
,TA_SPECIALIST_ID
,TA_MANAGER_ID
,PROGRAM_COUNT )
VALUES
(S.GRANT_ID
,S.GRANTEE_ID
,S.PROGRAM_ID
,S.REGIONAL_MANAGER_ID
,S.GRANT_SPECIALIST_ID
,S.PROGRAM_SPECIALIST_ID
,S.TA_SPECIALIST_ID
,S.TA_MANAGER_ID
,S.PROGRAM_COUNT);
