Re: an odd phenomenum for pseudocolumn

On Oct 10, 12:10 pm, Audison.Ath..._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?

It would have been beneficial if you would have posted the edition/version/platform where this works and does not work to improve the odds that someone can recognizes the problem and can post the bug#.

HTH -- Mark D Powell -- Received on Wed Oct 11 2006 - 12:54:53 CDT