Welcome and thank you for joining our new OpenText forum. Your questions, responses, best practices, and tips shared with other members will help make this channel vibrant. We're glad you're joining us and look forward to collaborating with you online.

Check out the Getting Started With OpenText Forums for tips on personalizing your experience.

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

Hello,


 


I have to make a  birt report with about 4 variables which should be used in query but not in standard way:


select * from table where field1=? and field2=? and field3=? and field4=?


but I need some conditions e.g.


if (var1 == null) {


select * from table where and field2=? and field3=? and field4=?;


}


if (var2 == null) {


select * from table where and field1=? and field3=? and field4=?;


}


etc...


It is possible to make such dynamic "where clause" in Birt ?


 


I use Eclipse BIRT Designer Version 2.6.2.


Thanks in advance for any help


Adam

 


 


Comments

  • edited October 22, 2014

    Hi Adam,


    Just modify your query like this



    select * from table
    where (field1 = ? or ? is null)
    and (field2 = ? or ? is null)
    and (field3 = ? or ? is null)
    and (field4 = ? or ? is null)

    I guess it should be enough


    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • 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 ?


     


    Adam


  • Yes, sure


    you can modify the query dynamicaly


    Your default qery will be



    select * from table 

    in the beforeOpen Event of your dataset time your test and add the WHERE condition to your query



    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;
    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • It works.Thanks a lot.


     


    Adam


  • I'm getting this error:


     


     


    org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.

    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')


     


    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')


  • Hi New_Bee,


     


    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.


     


    Can you copy your SQL query and explain a little bit more what part of the query you want to change


    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • Since this is for trial I'm using the dummy database CLASSICMODELS


     


     


    select *


    from CLASSICMODELS.EMPLOYEES


     


    To this I want to append a where clause such as:


     


    select *

    from CLASSICMODELS.EMPLOYEES


    where 1=1 and x=y


     


    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.


    I have a parameter 'OFFICECODE' which will hold the column name and parameter 'OfficeCodeValue' which will hold the value.


    In BeforeOpen I have the following code:


     


    var myWhere = " where (1=1) ";


    if (params["OfficeCodeValue"] != null)

                  myWhere += " and  " + params["OFFICECODE"] + " = " + params["OfficeCodeValue"];


    this.queryText += myWhere;


     


     


    OFFICECODE is of type String.


    And I'm passing values to the parameters in 'initalize' script.


     


     


    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.


  • be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it



    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;

    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • edited February 22, 2016

    Hi New_bee,


     


    check this example (Version 4.4)


    The table is the Customer table, just choose a string column and a value like COUNTRY and USA or UK or France....


     


    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


    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • Putting quote around the params["OfficeCodeValue"] solved my problem. I understood what I was doing wrong.


     


    Thank you so much for the quick response ! :)


     


    And thanks for creating  the rptdesign as well .


  • Great !


     


    You're welcome


    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • edited March 15, 2016


     


    be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it



    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;



    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. 


     


    But when I don't pass any value to the parameter I get no data at all in the report. 


    If no value is passed to the parameter then the 'if condition' check is not happening.

    No value to parameter should  mean  params["OfficeCodeValue"] = null and hence myWhere should not get appended. I'm unable to get this working


  • edited March 15, 2016


     


    be carreful, if params["OfficeCodeValue"] is a string, you have to put quote around it



    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null) {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;



    Actually, when I am not passing any value to the parameter then also the query text gets appended.

    The query  appears to be :


     


    select * from table_name

    where (1=1)  and  column_name = '';


  • edited March 15, 2016

    Hi,

     

    you should probably add a test like that one



    var myWhere = " where (1=1) ";
    if (params["OfficeCodeValue"] != null && params["OfficeCodeValue"] != '') {
    myWhere += " and " + params["OFFICECODE"] + " = '" + params["OfficeCodeValue"] + "'";
    }
    this.queryText += myWhere;

    With those 2 tests, you test the null value and also the blank value for your parameter


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