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
|
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)
|
Please ignore the last query with null pointer exception.... Different problem.
|
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
|
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
|
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
|
Main
webmaster@hxtt.com