The DBF driver seems to be a bit confused about derived tables....
select serviceDate,serviceDoctor from (
select bt.billdate as serviceDate,fkprovider as serviceDoctor
from bcline bc
join bctop bt
on bc.fkbctop = bt.pkguid
join bohipli bi
on bc.pkguid = bi.fkbcline
) this_ where serviceDate>'2004-02-01'
returns rows like the following
serviceDate serviceDoctor
-------------- ----------------
2004-02-02 -1182007141
2004-02-02 -1182007141
2004-02-02 -1182007141
2004-02-02 -1182007141
2004-02-02 -1182007141
2004-02-02 -1182007141
2004-02-02 -1182007141
.....
BUT the addtion of an outer left join to the derived table causes trouble!
select serviceDate,serviceDoctor from (
select bt.billdate as serviceDate,fkprovider as serviceDoctor
from bcline bc
join bctop bt
on bc.fkbctop = bt.pkguid
join bohipli bi
on bc.pkguid = bi.fkbcline
) this_
left outer join provider visualprac2_
on this_.serviceDoctor=visualprac2_.pkguid
where serviceDate>'2004-02-01'
serviceDate serviceDoctor
-------------- ----------------
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-05 -1182007141
2002-06-06 -1182007141
2002-06-06 -1182007141
2002-06-06 -1182007141
.....
Notice the incorrect dates!!
Help :)
Thanks,
Martin
|
Please note that this problem still exists even if the outer join is replaced by an inner join!
Thanks,
Martin
|
A serviceDate column should be in visualprac2_ table too. Please try:
select this_.serviceDate,serviceDoctor from ( select bt.billdate as serviceDate,fkprovider as serviceDoctor from bcline bc join bctop bt on bc.fkbctop = bt.pkguid join bohipli bi on bc.pkguid = bi.fkbcline ) this_ left outer join provider visualprac2_ on this_.serviceDoctor=visualprac2_.pkguid where this_.serviceDate>'2004-02-01'
The better solution is using always tableName.columnName in join sql:
select this_.serviceDate,this_.serviceDoctor from ( select bt.billdate as serviceDate,bc.fkprovider as serviceDoctor from bcline bc join bctop bt on bc.fkbctop = bt.pkguid join bohipli bi on bc.pkguid = bi.fkbcline ) this_ left outer join provider visualprac2_ on this_.serviceDoctor=visualprac2_.pkguid where this_.serviceDate>'2004-02-01'
|
Hi Support,
It isn't a problem of choosing the wrong column since there isn't any column in the provider table (aliased as visprac2). Even making explicit references to the tablename for the serviceDate does not make any difference. Playing around with aliases, etc.. didn't make a difference. The problem goes away when the join on the derived table is removed.
The incorrect dates returned actually appear to belong to the first rows of the table itself.... There is definitely a bug in the JDBC driver. This is an URGENT show stopper bug for us.
Thanks,
Martin
|
Recurred and fixed. Please download the latest package.
|
Thanks!
You guys always have an awesome turnaround time.
Oh, btw, I think you actually mean 'recreated and fixed', not 'recurred and fixed' :)
Martin
|