Re: Sort in Query

On 2 Jan., 16:32, Bram Mariën <b..._at_nospam.abiware.be> wrote:

> I've got a table with invoices with an unique invoice number.
> Contents of the table (SELECT INV_NR FROM INVOICES ORDER BY INV_NR) :
>
> 33070001
> 3307/0002
> 3307/0003
> 33070005
> 3307/0011
> 5151/0004

...
> In our invoice window, we have navigation buttons to go from one invoice
> to another (and back).
>
> For the 'next'-button, I have the following query :
> SELECT * FROM INVOICES WHERE INV_NR>'3307/0002' ORDER BY INV_NR
> and then I select the first record to be displayed.
>
> Previous is similar :
> SELECT * FROM INVOICES WHERE INV_NR<'3307/0003' ORDER BY INV_NR DESC
>
> Now, when I do the 'next' query, I get 33070001 (where inv_nr>'3307/0002'
> order by INV_NR)
> If I then go 'previous'
> (where inv_nr<'33070001' order by INV_NR DESC) I get 3307/0011...
>
> Hence, If I'm positioned on record 33070001, and I do next, I receive
> 33070005, and again next, I come to the end with record 5151/0004.
> So, there are several records skipped.
>
> It seems as if there's a difference in sorting with or without the
> where-clause.
>
> I've tried a lot of things, including 'ORDER BY CAST(INV_NR AS
> VARCHAR2(15))' and trying to cast the whereclause the same way, but
> without luck.
> I've took a look at the initialisation parameters of my
> Oracle Instance, but I don't understand much of these settings...

Bram,

I don't know if meanwhile your problem has been solved, but even if so,
just for the records:

If you order your records by INV_NR you expect that

      current INV_NR >= previous INV_NR
is true for each record. You expect this beacuse you assume that Oracle uses the same compare algorithm for sorting and comparing. Unfortunately (?), Oracle has different parameters for sorting and comparing:
NLS_SORT and NLS_COMP.

On my system i have

SQL> select * from nls_database_parameters   2 where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER                      VALUE
------------------------------ ---------------------
NLS_SORT                       GERMAN
NLS_COMP                       BINARY

and I can reproduce the behaviour you observe.

Once you issue e. g.

   alter session set nls_sort = binary;
   alter session set nls_comp = binary;
your problems should be solved.

Hth,

Urs Metzger Received on Mon Feb 25 2008 - 08:07:21 CST