Audison.Athena_at_gmail.com wrote:
> 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
>
Have you considered using ROW_NUMBER() OVER (ORDER BY ...).
This construct clearly ties the numbering to the order.
No wiggle room for the DBMS to mess it up...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
Received on Thu Oct 12 2006 - 08:19:28 CDT
