SQL Statement From Parameter, Report

Is it possible to pass something like:

‘IND=1 OR IND=2’

as a parameter in the report designer to a query like:


If so I must be doing something wrong. Thanks in advance.

You can’t pass the structure of a SQL query in as a parameter - the nature of parameters (and why they’re safe from SQL injection) is that they have to be an exact match of placeholders to values. So, SELECT * FROM PLANT WHERE IND = ? OR IND = ? would work fine - and you could do something with the logic calling your query to pass it the same value twice if there’s only one value, or something similar - but that’s the limitation of prepared statements.

You could also do a system.db call to execute an arbitrary query, in which case you could do something like this:
system.db.runUpdateQuery(‘SELECT * FROM PLANT WHERE %s’ % ‘IND = 1 OR IND = 2’)

But it’s very important to realize that this is not ‘safe’ from malicious user input - a user could input whatever arbitrary string they want to.

Thanks for your help Paul. I am having a mental block on how to do this, but I am trying to set up a nested query in the report


The user has the ability from a table on a window to select what plant(s), department(s), line(s) they want the report to query. I cannot figure out some way to pass a list of indexes to the report. I am trying to avoid scripting nested queries, but I did see an example on how to do this in the forum recently. Any other recommendations?

You could use a ‘Basic Query’ datasource, and then something like this:

SELECT example.note FROM example WHERE example.id IN ({Index})

While using a string parameter named Index. Then just pass in Index parameters as comma-separated values, ie: ‘1, 2, 3’ and you should see the indexes returned by your query.

The standard SQL Query datasource creates a prepared statement, so you won’t be able to do this value substitution (without nested queries).