Named Query with SQL Server

Ignition
1

I would like to pass in a list of machines to filter my named query but I can't seem to get this to work.

SELECT x,y,z
FROM my_table
WHERE x in :machines

I have machines as a value type parameter and am passing this string in like ('M1','M2','M3')
I have also tried moving the parenthesis to the query but no luck. Anyone know the best way to do this? I am trying to call this to get my dataset in a event script (Vision 8.1.25) The only way I have gotten the IN operator to work in the past like this was to construct the query using string concatenation but for this case I have a very long query and a bunch of different named queries to pick from conditionally so I thought it would make sense to use named queries here. If not, then I'll just construct it in the script like this

includedMachinesString = ''
for m in includedMachines:
if len(includedMachinesString) == 0:
includedMachinesString = "'" + m + "'"
else:
includedMachinesString = includedMachinesString + ', ' + "'" + m + "'"
dsDeptTrend = None
if len(includedMachinesString) > 0 :
includedMachinesString = '(' + includedMachinesString + ')'

  queryTrend = '''SELECT t_stamp, x,y,z FROM my_table WHERE machine in ''' + includedMachinesString
  argsTrend = []
  trendDept = system.db.runPrepQuery(queryTrend, argsTrend, 'Ignition_DB')
  trendDept = system.dataset.toDataSet(trendDept)
2

Well I think changing machines to a queryString has done the trick. I'm confirming now that I got what I am expecting but it looks right so far.

1 Like