Re: an odd phenomenum for pseudocolumn

Keith wrote:
> Audison.Athena_at_gmail.com wrote:
> > I heard this odd problem from my colleage, and from his oral
> > description of the problem I just outline the structure of this SQL
> > clause omitting any other trivial things as folllows:
> >
> > select b.*
> > from
> > (
> > select rownum row_id, a.*
> > from
> > (
> > select *
> > from table_ref1, table_ref2, table_ref3...
> > where complex condition
> > order by clause consist of several column
> > ) a
> > where rownum <= a specified_num1
> > ) b
> > where b.row_id > a specified_num2
> >
> > in one environment, it seems the result of this sql statement is
> > random, with some probability it gives the expected answer, however
> > sometimes it return empty set.
> > however in another environment, and the same program was executed with
> > no problem, each run gives the right answer.
> >
> > of course I know the same semantics of this clause can be implemented
> > by rank function, and it seems more fit to dispose this kind of
> > problem, here I want to know if this clause has any problem?if it has a
> > problem, it may be due to the pseudocolumn rownum, it appears in a
> > subquery select list and is aliased and refered at the containing query
> > where clause. If this pseudocolumn was substituted by a normal column,
> > then I promise no problem.
> > in the top-level query, the pseudocolumn in the subquery was referenced
> > as it's alias name, I don't know if oracle allow this kind of usage?
>
> Are you using at least Oracle 8i ???

The Oracle Edition is 9.2.0.4 Received on Wed Oct 11 2006 - 23:53:12 CDT