Re: SQL*PLUS and reports

>Hi,
>
>I come across some stange findings when trying to
>convert reportwriter 1.1 to reports 2.5 and would like
>any comments on it. We are running Oracle v.7.2.2.3.0
>on two machines, one running parallel server option and
>the other does not. Our SQL*PLUS engine 3.2.2.0.0
>with PL/SQL 2.2.2.3.0
>
>We have a report (in reportwriter 1.114.16.1) with
>sql statement:
> select staff_no, dept_num, xx_code, sum(amount)
> from etc etc
> group by xx_code, staff_no, dept_num
>
>Please note that there is no order by clause.
>Usually, it works but not in this case.
>I run on both machines and even extract the sql statement
>to run. The two machines give the the consistent wrong sorting
>order. One prints in strict order of staff_num while the
>other in order of dept_num and staff_num. Never in order of
>xx_code.
>But if I convert it to reports 2.5, it gives the CORRECT
>result!!
>
>Any comments on the anomaly??
>
>

There is not really a anomaly. The only way to GUARANTEE the order in which rows are returned is to put an ORDER BY clause. Without them, you will be grouped, as you requested, but not ordered. All the B's are together, they just might not be right after the A's.Which group gets first depends on (I don't know what - cached/not cached, which index the optimizer chooses?).

You get the same kind of effect if you select rows from a table with no order-by or where clause...the order may follow the insertion order, if they are in the same data block, but re-executing the same query may or may not give them in the same order.

Dan Hekimian-Williams Received on Wed Nov 11 1998 - 22:30:24 CST