How can I pass a parameter into a time interval query (i.e. where timestamp > sysdate - ? minute

JMcGrath

I've been working on a report which allows the user to pick a lag time for some summary statistics but the concept hinges on the use of a parameter in the where clause. As I stated in the topic title the query is similar to :


select *
from data_table
where timestamp > sysdate - interval ? days


I've gotten the same query to work when I replace the ? with '3' or any other number, but it will not work with a ?

Any thoughts?

mwilliams

If you put in the ? and in the parameter section of the dataSet editor, you make the default value 3, does it work?

You could also just build the where clause in the beforeOpen script of your dataSet. You'd make your query in the dataSet editor just the select/from portion. Then, in your beforeOpen script of your dataSet, you'd be able to add the where clause with something like:

this.queryText = this.queryText + " where timestamp > sysdate - interval " + params["paramName"] + " days";