Problem in date or parameter or IN clause

BIRTigo

Hi,
In BIRT 2.2, i have this query
Query Statement:
select c.cust_id, sum(o.order_amount)
from customer as c, order as o
where cust_id = order_id
and (o.order_items_col1 IN (?) or o.order_items_col1 IN (?))
and o.order_date between (?) and (?)
group byc.cust_id

This query gives me empty values. but if i add this in the Property Binding:
if (params["IP_Address"].equals("*")) {
this.queryText = "select c.cust_id, sum(o.order_amount) from customer as c, order as o";
} else {
var endOfQuery = "WHERE cust_id = order_id and (o.order_items_col1 IN (" + params["Item_codes"] +" ) or o.order_items_col1 IN (" + params["Item_codes"] +" )) and o.order_date between (" + params["oder_start_date"] +" ) and (" + params["oder_end_date"] +" ) group byc.cust_id"";
this.queryText = "select c.cust_id, sum(o.order_amount) from customer as c, order as o " + endOfQuery;
}


I get this error:

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.2.1.r22x_v20070925
Error Code:odaconsumer.CannotPrepareStatement
Error Message:Failed to prepare the following query for the data set type org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet.
[c.cust_id, sum(o.order_amount)
from customer as c, order as o
where cust_id = order_id
and (o.order_items_col1 IN ('123', '234') or o.order_items_col1 IN ('123', '234')) and o.order_date between (Thu May 22 14:56:00 GST 2008) and (Thu May 22 15:00:00 GST 2008 ) group byc.cust_id
Error preparing SQL statement.
SQL error #1: Syntax error: Encountered "May" at line 1, column 425.

But if in Query I put this:
select c.cust_id, sum(o.order_amount) from customer as c, order as o
where cust_id = order_id and (o.order_items_col1 IN ('123', '234') or o.order_items_col1 IN ('123', '234')) and o.order_date between (?) and (? ) group byc.cust_id"

It is giving me the correct output.

Please help.:(

Virgil Dodson

Hi BIRTigo,

I found the similar situation in the property binding tab... and had to make sure some single quotes were passed in like below:

OLD
o.order_date between ("+params["oder_start_date"]+") and ("+params["oder_end_date"]+")

NEW
o.order_date between ('"+params["oder_start_date"]+"') and ('"+params["oder_end_date"]+"')

[Look closely - I added a single quote directly after each "(" and directly before each ")"]