Dynamic query - whole where clause (not standard field1=?)
Options
<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>
<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>
0
Comments
-
<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 ipb0 -
<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 ipb0 -
<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>
0 -
<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 ipb0 -
<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>
0 -
<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 ipb0 -
<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 ipb0 -
<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>
0 -
<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>
0 -
<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 ipb0
Categories
- All Categories
- 111 Developer Announcements
- 49 Articles
- 103 General Questions
- 123 Services
- 40 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 901 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 127 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 1 XM Fax