AnsweredAssumed Answered

Invalid column index - SQL Query Node

Question asked by Sabthami S on May 2, 2017

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

Outcomes