Hey Guys !!
Interestingly, I don't think there is any problem with the original queries
being discussed ...
select * from emp
/
EMPNO
1
2
3
4
4 rows selected.
select *
from (
select *
from (
select *
from (
select *
from (select distinct empno
from emp)
where empno = 3
)
where rownum < 2
)
)
/
EMPNO
3
1 row selected.
I tried moving the "where rownum < 2" to a level below... still it works
fine... wots the prob ???
-Mahesh.
"Anurag Varma" <avdbi_at_hotmail.com> wrote in message
news:umbbvn51i6qva1_at_corp.supernews.com...
> Quite true. Saw this strange behavior in 8.1.6.3
>
> More tests:
> 1 select r, id from (
> 2 select rownum r, id from (
> 3 select id from (select distinct t.id from t)
> 4 where id=4
> 5 ) where rownum < 4
> 6* )
> SQL> /
>
> R ID
> ---------- ----------
> 1 4
>
> 1 select r, id from (
> 2 select rownum r, id from (
> 3 select id from (select distinct t.id from t)
> 4 -- where id=4
> 5 ) where rownum < 4
> 6* )
> SQL> /
>
> R ID
> ---------- ----------
> 1 1
> 2 2
> 3 3
>
> If its merging these two queries then the above results are just
contradictory.
> I just cannot explain how oracle is processing them?
>
> Anurag
>
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3d650f41$0$237$ed9e5944_at_reading.news.pipex.net...
> > 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 Fri Aug 23 2002 - 05:15:33 CDT
