Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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