DataAdapter retrieves no row from Oracle left join count query

  • Thread starter Thread starter tonixlaw
  • Start date Start date
T

tonixlaw

Hi guys,

In vb.net, i use OracleDataAdapter to execute following query and fill
into a DataSet. The problem is it returns no row but actually it should
have some rows. If you run this query in sql plus or similiar tool, it
works fine. This problem happens only when the first table has rows
that the second table can not match, under normal circumstance this
query should return some rows with f2 = 0.

select ta.id, count(tb.id) as f2
from ta left join tb
on ta.id = tb.id
group by ta.id

So if you change "left join" into "inner join", it works fine. I highly
suspect this is a bug of the .NET client provider for oracle.

Currently my workaround is making an ugly query like this:

select ta.id, count(tb.id) as f2
from ta INNER join tb
on ta.id = tb.id
group by ta.id
UNION
select ta.id, 0 as f2
from ta
where not exists
(select * from tb
where ta.id = tb.id)

I am using Oracle 9i.

Anyone has similiar experience or some idea on solving this problem?
Thanks.

Tonix
 
Back
Top