an odd phenomenum for pseudocolumn

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? Received on Tue Oct 10 2006 - 11:10:35 CDT