Hi All,
We have upgraded our system from 6.9.1 to 7.0.1 and we are in process of validating few workflows.We have a workflow which got stopped in one of SQL Query node reflecting the following error in the WorkPoint.log file.I have already read few posts on the community regarding Index Column Index error and removed the q'''' part in the highlighted statement of the SQL query and validated the same workflow but still we got the same error on the WorkPoint.log file.
Please find the below Error information and the SQL Query of the particular node for more reference.Any help on this is much appreciated !!!
ERROR IN WORKPOINT.LOG:
2017-04-26 14:09:01,489 [Worker_actionq#ActionQ1#WPDS_16] ERROR com.workpoint.server.script.ScriptEngine - A script error has occurred.
Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 40590:WPDS, Process Ref = '133',
Node Name = 'Collect Mapping Values', WorkItem = 269966:WPDS:1
java.sql.SQLException: Invalid column index
SQL QUERY OF THE COLLECT MAPPING VALUES NODE:
select
CASE
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='London'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC Europe Ltd' THEN
CASE
WHEN '${access_request_cri_meu_department}'='TD' then
(Select ADContainer from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department='TD')
ELSE
(Select ADContainer from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department is null)
END
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='New York'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC' THEN
CASE
WHEN '${access_request_cri_meu_department}'='Treasury Unit' then
(Select ADContainer from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department='Treasury Unit')
ELSE
(Select ADContainer from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department is null)
END
ELSE ADContainer
END AS ADContainer,
CASE
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='London'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC Europe Ltd' THEN
CASE
WHEN '${access_request_cri_meu_department}'='TD' then
(Select UserContainer from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department='TD')
ELSE
(Select UserContainer from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department is null)
END
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='New York'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC' THEN
CASE
WHEN '${access_request_cri_meu_department}'='Treasury Unit' then
(Select UserContainer from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department='Treasury Unit')
ELSE
(Select UserContainer from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department is null)
END
ELSE UserContainer
END AS UserContainer,
CASE
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='London'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC Europe Ltd' THEN
CASE
WHEN '${access_request_cri_meu_department}'='TD' then
(Select UNCPath from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department='TD')
ELSE
(Select UNCPath from IAM_NHR.SiteCodes where Location='London' and Company='SMBC Europe Ltd' and Department is null)
END
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='New York'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC' THEN
CASE
WHEN '${access_request_cri_meu_department}'='Treasury Unit' then
(Select UNCPath from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department='Treasury Unit')
ELSE
(Select UNCPath from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department is null)
END
ELSE UNCPath
END AS UNCPath,
ADDomain, BackupNotesServer, CACertifier, Company,
CASE
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='London' THEN
CASE
WHEN substr((select q''${jobUserData_ACCTLASTNAME}'' from dual),1,1) between 'A' and 'L' THEN 'GBLOUsers1'
ELSE 'GBLOUsers2'
END
WHEN '${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'='New York'
AND '${access_request_cri_meu_CUS_ATTR_USER_CAS_6}'='SMBC' THEN
CASE
WHEN '${access_request_cri_meu_department}'='Treasury Unit' then
(Select DefaultMailGroup from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department='Treasury Unit')
ELSE
(Select DefaultMailGroup from IAM_NHR.SiteCodes where Location='New York' and Company='SMBC' and Department is null)
END
ELSE DefaultMailGroup
END as DefaultMailGroup,
Department, EmailDomain, InternetEmailConstruction,
Location, LogonDomain, MailFileTemplate, MailPolicy, NotesDomain,
NotesIDVault, PrimaryNotesServer, ConfigNotesServer
from IAM_NHR.SITECODES
Where Company='${access_request_cri_meu_CUS_ATTR_USER_CAS_6}' and
Location='${access_request_cri_meu_CUS_ATTR_USER_CAS_33}'
and Department is null
Many thanks in advance !!!
Regards,
Sabthami Subramanian