It does seem a bit odd. FWIW your test does NOT reproduce on 8.1.7 or 9.2 on
Win2000. Perhaps an upgrade would resolve the issue.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Gui" <koen.janssens_at_alcatel.be> wrote in message
news:11ecf2b.0208220805.29a6e51b_at_posting.google.com...
> Hello all.
>
> I am experiencing some weird query behavior trying to get the to N
> rows of a view that contains a 'distinct' select. Although the real
> life query is a bit more complex, i found a simple sample query that
> has the same problem. I know this query is not very usefull an not
> optimal, but its just to make a point:
>
> I have one table containing one integer field. The table contains 4
> rows:
>
> create table t (id numeric);
> insert into t values (1);
> insert into t values (2);
> insert into t values (3);
> insert into t values (4);
>
> Then i do a simple select:
>
> select * from (select distinct id from t) where id=3
>
> This returns '3' as expected. Note that the 'select distinct id from
> t' is a nested query.
>
> Now i only want to retrieve the first row of the result set of the
> previous query:
>
> select * from (
> select * from (select distinct id from t) where id=3
> ) where rownum < 2
>
> This still returns '3', again as expected.
>
> Now i wrap another select * around this query:
>
> select * from (
> select * from (
> select * from (select distinct id from t) where id=3
> ) where rownum < 2
> )
>
> For some mysterious reason, this query does not return any rows
> anymore.
>
> The only reason i can think of it that oracle merges the two where
> clauses into one (id = 3 and rownum < 2). This would explain why i
> don't see any results. But i have no idea why oracle does this in the
> last query but not in the second one.
>
> Any ideas ? Am i missing something here ?
>
> We are using oracle 8.1.6 solaris edition.
>
> PS: If i add a rownum to the select part of the query, it works again:
> select * from (
> select * from (
> select rownum r, q.* from (select distinct id from t) q where id=3
> )q where rownum < 2
> )
> returns '3'. Don't ask me why....
Received on Thu Aug 22 2002 - 11:20:17 CDT
