Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
Derived Table Fun
Martin Ross
2006-04-13 11:49:51
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

Re:Derived Table Fun
Martin Ross
2006-04-13 12:20:59
Please note that this problem still exists even if the outer join is replaced by an inner join!

Thanks,

Martin
Re:Re:Derived Table Fun
HXTT Support
2006-04-13 19:01:21
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'
Re:Re:Re:Derived Table Fun
Martin Ross
2006-04-14 12:24:06
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
Re:Re:Re:Re:Derived Table Fun
HXTT Support
2006-04-14 20:44:06
Recurred and fixed. Please download the latest package.
Re:Re:Re:Re:Re:Derived Table Fun
Martin Ross
2006-04-15 07:48:22
Thanks!

You guys always have an awesome turnaround time.

Oh, btw, I think you actually mean 'recreated and fixed', not 'recurred and fixed' :)

Martin

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Refund | Sitemap