Dynamic query - whole where clause (not standard field1=?)

Adam11
edited February 11, 2022 in Analytics #1
<p>Hello,</p>
<p> </p>
<p>I have to make a  birt report with about 4 variables which should be used in query but not in standard way:</p>
<p>select * from table where field1=? and field2=? and field3=? and field4=?</p>
<p>but I need some conditions e.g.</p>
<p>if (var1 == null) {</p>
<p>select * from table where and field2=? and field3=? and field4=?;</p>
<p>}</p>
<p>if (var2 == null) {</p>
<p>select * from table where and field1=? and field3=? and field4=?;</p>
<p>}</p>
<p>etc...</p>
<p>It is possible to make such dynamic "where clause" in Birt ?</p>
<p> </p>
<p>I use Eclipse BIRT Designer Version 2.6.2.<br><br>
Thanks in advance for any help<br><br>
Adam<br>
 </p>
<p> </p>

Comments

  • GLO_FR
    edited October 22, 2014 #2
    <p>Hi Adam,</p>
    <p>Just modify your query like this</p>
    <pre class="_prettyXprint _lang-sql">
    select * from table
    where (field1 = ? or ? is null)
    and (field2 = ? or ? is null)
    and (field3 = ? or ? is null)
    and (field4 = ? or ? is null)
    </pre>
    <p>I guess it should be enough</p>
    Warning No formatter is installed for the format ipb
  • <p>Thanks for the answer, I know this method but sql query last a lot time becouse I used finally not 4 but 10 filters. So it is not possible to do it another way ?</p>
    <p> </p>
    <p>Adam</p>
  • <p>Yes, sure</p>
    <p>you can modify the query dynamicaly</p>
    <p>Your default qery will be</p>
    <pre class="_prettyXprint _lang-sql">
    select * from table </pre>
    <p>in the beforeOpen Event of your dataset time your test and add the WHERE condition to your query</p>
    <pre class="_prettyXprint _lang-js">
    var myWhere = " where (1=1) ";
    if (params["param1"] != null) myWhere += " and field1=" + params["param1"];
    if (params["param2"] != null) myWhere += " and field2=" + params["param2"];
    if (params["param3"] != null) myWhere += " and field3=" + params["param3"];
    if (params["param4"] != null) myWhere += " and field4=" + params["param4"];
    this.queryText += myWhere;
    </pre>
    Warning No formatter is installed for the format ipb
  • <p>It works.Thanks a lot.</p>
    <p> </p>
    <p>Adam</p>
  • <p>I'm getting this error:</p>
    <p> </p>
    <p> </p>
    <p>org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.<br>
    SQL error #1:Comparisons between 'VARCHAR (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')</p>
    <p> </p>
    <p>java.sql.SQLSyntaxErrorException: Comparisons between 'VARCHAR (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')</p>
  • <p>Hi New_Bee,</p>
    <p> </p>
    <p>it seems that you have problem with comparaison i.e. you're trying to compare INT with CHAR or Date or a filed which have not the same type/format.</p>
    <p> </p>
    <p>Can you copy your SQL query and explain a little bit more what part of the query you want to change</p>
    Warning No formatter is installed for the format ipb
  • <p>Since this is for trial I'm using the dummy database CLASSICMODELS</p>
    <p> </p>
    <p> </p>
    <p><em>select *</em></p>
    <p><em>from CLASSICMODELS.EMPLOYEES</em></p>
    <p> </p>
    <p>To this I want to append a where clause such as:</p>
    <p> </p>
    <p><em>select *<br>
    from CLASSICMODELS.EMPLOYEES</em></p>
    <p><em>where 1=1 and x=y</em></p>
    <p> </p>
    <p>now 'x' is column name and 'y' is the  value - Both these will actually come from the front end and be passed on to the respective parameters.<br><br>
    I have a parameter 'OFFICECODE' which will hold the column name and parameter 'OfficeCodeValue' which will hold the value.</p>
    <p>In BeforeOpen I have the following code:</p>
    <p> </p>
    <p><em>var myWhere = " where (1=1) ";</em></p>
    <p><em>if (params["OfficeCodeValue"] != null)<br>
                  myWhere += " and  " + params["OFFICECODE"] + " = " + params["OfficeCodeValue"];</em></p>
    <p><em>this.queryText += myWhere;</em></p>
    <p> </p>
    <p> </p>
    <p>OFFICECODE is of type String.</p>
    <p>And I'm passing values to the parameters in 'initalize' script.</p>
    <p> </p>
    <p> </p>
    <p>Similarly, I'll add more parameters and if values are coming from front end then the not null condition will be checked for those parameters and where clause will be added accordingly.</p>
  • <p>be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it</p>
    <pre class="_prettyXprint _lang-auto _linenums:0">
    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;
    </pre>
    Warning No formatter is installed for the format ipb
  • GLO_FR
    edited February 22, 2016 #10
    <p>Hi New_bee,</p>
    <p> </p>
    <p>check this example (Version 4.4)</p>
    <p>The table is the Customer table, just choose a string column and a value like COUNTRY and USA or UK or France....</p>
    <p> </p>
    <p>The script is pretty easy so you can define the final query (where clause, join, order...) the way you want using as many paramter as needed</p>
    Warning No formatter is installed for the format ipb
  • <p>Putting quote around the params["OfficeCodeValue"] solved my problem. I understood what I was doing wrong.</p>
    <p> </p>
    <p>Thank you so much for the quick response ! :)</p>
    <p> </p>
    <p>And thanks for creating  the rptdesign as well .</p>
  • <p>Great !</p>
    <p> </p>
    <p>You're welcome</p>
    Warning No formatter is installed for the format ipb
  • New_Bee
    edited March 15, 2016 #13
    <blockquote class="ipsBlockquote" data-author="GLO_FR" data-cid="142412" data-time="1456139218">
    <div>
    <p> </p>
    <p>be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it</p>
    <pre class="_prettyXprint _lang-auto _linenums:0">
    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;
    </pre>
    </div>
    </blockquote>
    <p>When I pass a value to the parameter in the above query script, I get the desired data in the report as per the param. </p>
    <p> </p>
    <p>But when I don't pass any value to the parameter I get no data at all in the report. </p>
    <p>If no value is passed to the parameter then the 'if condition' check is not happening.<br>
    No value to parameter should  mean  params["OfficeCodeValue"] = null and hence myWhere should not get appended. I'm unable to get this working</p>
  • New_Bee
    edited March 15, 2016 #14
    <blockquote class="ipsBlockquote" data-author="GLO_FR" data-cid="142412" data-time="1456139218">
    <div>
    <p> </p>
    <p>be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it</p>
    <pre class="_prettyXprint _lang-auto _linenums:0">
    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;
    </pre>
    </div>
    </blockquote>
    <p>Actually, when I am not passing any value to the parameter then also the query text gets appended.<br>
    The query  appears to be :</p>
    <p> </p>
    <p><em>select * from table_name<br>
    where (1=1)  and  column_name = '';</em></p>
  • GLO_FR
    edited March 15, 2016 #15
    <p>Hi,<br>
     <br>
    you should probably add a test like that one</p>
    <pre class="_prettyXprint _lang-auto _linenums:0">
    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null && params["OfficeCodeValue"] != '') {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;
    </pre><p>With those 2 tests, you test the null value and also the blank value for your parameter</p>
    Warning No formatter is installed for the format ipb