Replacing where clause dynamically in query

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 !


Comments

  • edited 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)


    Jeff F.
    Customer Support Engineer I
    OpenText
  • 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? 

  • 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



    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; 

    Take a look on this thread here

    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
Sign In or Register to comment.