Re: Sort in Query

From: <fitzjarrell_at_cox.net>
Date: Wed, 2 Jan 2008 08:15:36 -0800 (PST)
Message-ID: <c3dda05c-6691-43aa-9199-c314c020fd02@v32g2000hsa.googlegroups.com>


On Jan 2, 9:32 am, Bram Mariën <b..._at_nospam.abiware.be> wrote:
> Hello,
>
> Oracle 9i 9.2.0.1.0 on Windows 2003 Server.
> Client = Omnis Studio on windows xp pro sp2.
>
> Being an Omnis developer used to SQL Server 2005, I'm now put up with the
> task to make our application work with Oracle 9i as a backend.
>
> I've managed to make everything work pretty ok, but I'm stuck on a sorting
> issue.
>
> 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
>
> (results from SQL*Plus, differences between invoice numbers with an
> without / is correct).
>
> 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...
>
> I'm sorry if the problem isn't described that well, my english isn't my
> native language...

It appears this is an ASCII collation 'issue', which may not be easily 'fixable'. Why do some of the entries contain the '/' and others not?

David Fitzjarrell Received on Wed Jan 02 2008 - 10:15:36 CST

Original text of this message