nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Replacing where clause dynamically in query

New_Bee

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 !

Find more posts tagged with

Comments

jfranken

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

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

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>