Report Query- Table name as parameter (7.9)

In the Reporting Module, I have a script that determines which table the data is in, then sets data[‘db_table’] = ‘myTable’.

Then the query runs after the script, but gives an error saying “@p0” is not defined, even if I give it a default value. The xml also shows the correct table name in the preview.

Any suggestions?

SQL will not accept schema as a parameters, so I doubt you would be able to make this work with a standard query. Assuming that I am understanding what you have in your query.

SELECT * FROM ?
WHERE t_stamp >= ? AND t_stamp <= ?

I am querying transaction groups, there are 4 tables, one for each “system” in the warehouse.

You can’t use ‘?’ substitution for table names, column names, etc. Those are schema identifiers. You have to use string substitution. In a named query, this would be a parameter of type “QueryString” instead of type “Value”.

https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters

Be aware that using strings for schema may be allowed, but it is a potential security hole. Don’t supply user-supplied strings to QueryString parameters.

1 Like

Since you flagged this as 7.9, you may or may not have access to named queries.

You can also accomplish this within a script data source.

Here is a decent thread that covers a similar issue:

We are using 7.9.3, so I do not have named queries

That worked. Thank you