Is it possible to pass something like:
‘IND=1 OR IND=2’
as a parameter in the report designer to a query like:
SELECT * FROM plant WHERE ?
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
Plant
Department
Line
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).