Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
Derived Table Broken!
Martin Ross
2006-08-09 20:04:07.0
This query returns correct rows for the join...

select *
from bcline bc
join bctop bt
on bc.fkbctop = bt.pkguid
join bohipli bi
on bc.pkguid = bi.fkbcline
inner join provider p
on fkprovider = p.pkguid


whereas what should be a logically identical query returns 0 rows.


select * from
(
select *
from bcline bc
join bctop bt
on bc.fkbctop = bt.pkguid
join bohipli bi
on bc.pkguid = bi.fkbcline
) t
inner join provider p
on t.fkprovider = p.pkguid

pkguid, fkbctop, fkbcline, and fkprovider are all ints.

Thanks,

Martin



Re:Derived Table Broken!
Martin Ross
2006-08-09 20:12:31.0
When I try this query I get

select top 50 * from
(
select bc.fkprovider as serviceDoctor
from bcline bc
join bctop bt
on bc.fkbctop = bt.pkguid
join bohipli bi
on bc.pkguid = bi.fkbcline
) t
join provider p
on t.serviceDoctor = p.pkguid


java.lang.NullPointerException
at com.hxtt.sql.common.n.a(Unknown Source)
at com.hxtt.sql.du.u(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.c0.a(Unknown Source)
at com.hxtt.sql.bf.a(Unknown Source)
at com.hxtt.sql.bf.a(Unknown Source)
at com.hxtt.sql.ac.a(Unknown Source)
at com.hxtt.sql.ac.a(Unknown Source)
at com.hxtt.sql.ac.execute(Unknown Source)
at NMFL.ur.a(Unknown Source)
at NMFL.sm.run(Unknown Source)
Re:Re:Derived Table Broken!
Martin Ross
2006-08-09 20:15:16.0
Please ignore the last query with null pointer exception.... Different problem.
Re:Re:Re:Derived Table Broken!
Martin Ross
2006-08-09 20:17:21.0
This query returns rows.....

select top 50 * from
(
select bt.fkprovider
from bcline bc
left join bohipli bi
on bc.pkguid = bi.fkbcline
left join bctop bt
on bc.fkbctop = bt.pkguid
) t
join provider p
on p.pkguid = t.fkprovider
Re:Re:Re:Re:Derived Table Broken!
HXTT Support
2006-08-12 04:46:58.0
In my test, the first 3 sql passted test with correct 8 rows:
select * from bcline bc join bctop bt on bc.fkbctop = bt.pkguid join bohipli bi on bc.pkguid = bi.fkbcline inner join provider p on fkprovider = p.pkguid

select * from ( select * from bcline bc join bctop bt on bc.fkbctop = bt.pkguid join bohipli bi on bc.pkguid = bi.fkbcline ) t inner join provider p on t.fkprovider = p.pkguid

select top 50 * from ( select bc.fkprovider as serviceDoctor from bcline bc join bctop bt on bc.fkbctop = bt.pkguid join bohipli bi on bc.pkguid = bi.fkbcline ) t join provider p on t.serviceDoctor = p.pkguid

The last sql will throw java.lang.NullPointerException:
select top 50 * from ( select bt.fkprovider from bcline bc left join bohipli bi on bc.pkguid = bi.fkbcline left join bctop bt on bc.fkbctop = bt.pkguid ) t join provider p on p.pkguid = t.fkprovider
Re:Re:Re:Re:Re:Derived Table Broken!
HXTT Support
2006-08-12 05:11:29.0
The next version will throw "java.sql.SQLException: Invalid column: bt.fkprovider" to replace the java.lang.NullPointerException for
select top 50 * from ( select bt.fkprovider from bcline bc left join bohipli bi on bc.pkguid = bi.fkbcline left join bctop bt on bc.fkbctop = bt.pkguid ) t join provider p on p.pkguid = t.fkprovider

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap