Replacing where clause dynamically in query

New_Bee · February 19, 2016

In my report query I have a where clause that needs to be replaced dynamically based on the data chosen in the front end. 
The query is something like : 

<select statement>
where ?=?
 
 
I already have a code to replace the value - I created report parameter and linked to the value ? in the query. 
Example: <select statement> 
where name=?
 
Any value of name that comes from front end replaces the ? in the where clause - this works fine.
 
 
But now I need to replace the entire clause. Should I create two parameters and link them to both the '?' ? 
Can anyone help with this? 
Thanks !

jfranken · February 19, 2016

One option is to change your query to:
 
select x from y where myplaceholder1 = 'myplaceholder2'
 
Then add the parameter values in code. That is done by selecting the data set and clicking the Script tab. Go to the beforeOpen event and enter the following:
 
this.queryText = this.queryText.replace("myplaceholder1", params["p1"].value).replace("myplaceholder2", params["p2"].value);
 
(replace "p1" and "p2" with the names of your two parameters)

New_Bee · February 22, 2016

Thanks for responding. 
But in my report the input parameters are not fixed, as in there could be one or two or even no input parameter. How do I alter the query based on the number of input parameters?

GLO_FR · February 22, 2016

Hi New_Bee,
 
defining a query dynamically is done with coding i.e. you can add or replace part of the query depending on many paramters.
for instance you can have things like
<pre class="_prettyXprint _lang-">
var myWhere = " where (1=1) ";
if (params["p1"] == 'A' && params["p2"] == 'B') var myWhere = " and field1=" + params["p3"];
if (params["p1"] == 'A' && params["p2"] != 'B') var myWhere = " and field1=" + params["p4"];
if (params["p1"] != 'A' && params["p2"] == 'B') var myWhere = " and field1=" + params["p5"];
if (params["p1"] != 'A' && params["p2"] != 'B') var myWhere = " and field1=" + params["p6"];
this.queryText += myWhere; </pre>
<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/35490-dynamic-query-whole-where-clause-not-standard-field1/'>Take a look on this thread here</a>

Replacing where clause dynamically in query

Comments

Categories