Josh_Pearson_8518

Use of MULTI variable object in bind parameters

Discussion created by Josh_Pearson_8518 on Oct 25, 2016
Hello -
  I'm running into the following issue this morning for which I cannot find an easy solution.

In my environment, I have a configuration database that contains the parameters I use for individual jobs.  I'm building out some new functionality where I have three separate Automic variable objects that attempt to pull out some of that data.  Here's what I have.

1) VARA.IB.PROCESS.TYPES - A STATIC variable object that contains a list of processes from the configuration table as well as some other data such as Automic workflow templates that should be submitted.  Here is a sample of what that variable contains:

Key         Value 1
Process1  IB.TEMPLATE
Process2  IB.TEMPLATE
Process3  IB.TEMPLATE

2) VARA.PROCESS_NAME - A MULTI variable object that I use to pull out a list of process names from VARA.IB.PROCESS.TYPES.  I'm sort of cheating here.  This variable's purpose is really to supply a list of process names to the next variable object listed below.  Inside this variable object, for both Variable 1 and Variable 2, I'm inputting the above referenced variable (VARA.IB.PROCESS.TYPES), using a union operation.  I then take the results and format it as such:
    "or processname like '{1}%' "

This results in the variable object pulling out process names and formatting them into SQL snippets to be used by the next job.

3) VARA.PULL_PROCESS_CONFIGURATIONS - A SEC_SQL variable object that uses VARA.PROCESS_NAME as a bind parameter.  This is the contents of that SQL variable can be found below.  For both bind parameters ('?'), I'm referencing VARA.PROCESS_NAME.  This query should pull out a list of processes, file paths, and file names from my configuration table.  However, when I attempt to evaluate this variable, I'm getting the error "U2012033 Query could not be executed: 'Incorrect syntax near '@P0'.'"

Any idea what I could be doing wrong here?  Maybe there's a better way of doing this?  The end result needs to be that I pull the first column out of a static variable and feed it into a SQL statement.  This was the best way I could think of doing this.
SELECT DISTINCT T1.[Path], T2.[FileName], T2.BusinessID, T2.ProcessName FROM
(
SELECT Value AS 'Path', BusinessID, ProcessName FROM config.parameter WHERE
ParameterID IN ( 2 ) 
AND 
(
ProcessName = 'PlaceHolder'
?
) --GROUP BY BusinessID
) T1

LEFT JOIN

(
SELECT Value AS 'FileName', BusinessID, ProcessName FROM config.parameter WHERE
ParameterID IN ( 1 ) 
AND 
(
ProcessName = 'PlaceHolder'
?
) --GROUP BY BusinessID
) T2

ON
   T1.BusinessID = T2.BusinessID AND T1.ProcessName = T2.ProcessName
   
   ORDER BY BusinessID

Outcomes