Ok, I tinkered around some and determined that if I coerce all input
parameters of type SQL_TYPE_DATE to SQL_TIMESTAMP that it will work.

Can anyone think of any reason why a DATE only field couldn't be handled as
a regular TIMESTAMP field?

I'm thinking of adding a property called CoerceDateParamsToTimestamp to
handle this. I'm wondering if I should just add it as a global flag, a
session property, a connection property or a statement property.

I'm also wondering what the default value should be.

Thoughts?

Jason

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of IBO Support List
Sent: 06 June 2012 07:45 PM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Bugs: Strange query result that involve timestamp field
with date params

Here is what I tried and it worked:

select d.tanggal,
sum(d.registrasi) registrasi,
sum(d.visit) visit,
sum(d.training) training,
sum(d.registrasi+d.visit+d.training) CalLR
from ( select pm."tglBayar" tanggal, sum(pm."total") registrasi, 0 visit, 0
training
from "tbPersonM" pm
where pm."tglBayar" between :tgl1 and :tgl2
group by pm."tglBayar"
union all
select cast(pv."tglVisit" as date) tanggal, 0 registrasi,
sum(pv."total") visit, 0 training
from "tbPersonV" pv
-- I added in the cast() on the line below.
where cast(pv."tglVisit" as date) between :tgl1 and :tgl2
group by cast(pv."tglVisit" as date)
union all
select bt."tglBayar" tanggal, 0 registrasi, 0 visit, sum(bt."total")
training
from "tbBayarTraining" bt
where bt."tglBayar" between :tgl1 and :tgl2
group by bt."tglBayar"
) d
group by d.tanggal
order by d.tanggal



-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of IBO Support List
Sent: 06 June 2012 07:43 PM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Bugs: Strange query result that involve timestamp field
with date params

Ok, I've looked into this more and the multiple parameters need to be of the
same underlying SQL type in order to all become bound together. You have
some of them as DATE and some of them as TIMESTAMP and IBO cannot
consolidate them all to a single buffer space.

I'm looking into a way that perhaps I can detect this and use parameter type
coercion in order to make this work. But, I'm not so sure I'll be able to
accomplish this.

You may want try and cast the columns or the parameters to force them all to
be of the same type.

Jason




------------------------------------

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links





------------------------------------

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links