Subject RE: [IBO] Bugs: Strange query result that involve timestamp field with date params
Author IBO Support List
Here is what I tried and it worked:

select d.tanggal,
sum(d.registrasi) registrasi,
sum(d.visit) visit,
sum( training,
sum( CalLR
from ( select pm."tglBayar" tanggal, sum(pm."total") registrasi, 0 visit, 0
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")
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: [] On Behalf
Of IBO Support List
Sent: 06 June 2012 07:43 PM
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.



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