As part of my jobs, I need to writes SQL queries to connect to our PI database. To generate a query, I need to pass an array of tags (essentially primary keys), but these have to be inserted as strings.

As this will be a modular query and used for multiple tags, a placeholder is being used.

The query relies upon the use of the WHERE IN statement, which is where the placeholder is, like below:

SELECT SUM(value * 5/1000) as "Hourly Flow [kL]" from piarchive..pitotal 
WHERE tag IN (?) AND time between ? and ? and timestep = '1d' and calcbasis = 'Eventweighted' and value <> ''

The issue is the format in which the tags are to be passed in as. If I add them directly into the query (for testing), they go in the format (these are example numbers): '000000012','00000032','005050236','4560236' and the query looks like:

SELECT SUM(value * 5/1000) as "Hourly Flow [kL]" from piarchive..pitotal 
WHERE tag IN ('000000012','00000032','005050236','4560236') AND time between ? and ? and timestep = '1d' and calcbasis = 'Eventweighted' and value <> '' 

which works.

If I try and add the same tags into the placeholder, the query fails. If I only add 1 tag, with no quotes (using the placeholder), the query works.

Why is this happening? Is there anyway around it?