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

Tony - are you specifying any parameters in this query?
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

No parameter.

What results do you get when you try executing the queries below through
SQL+?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
----------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Through SQL+, it's normal.

Tonix