Re: Sort in Query
Date: Wed, 2 Jan 2008 08:15:36 -0800 (PST)
On Jan 2, 9:32†am, Bram MariŽn <b..._at_nospam.abiware.be> wrote:
> Oracle 9i 184.108.40.206.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
> 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) :
> (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
> 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