Sort in Query

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... Received on Wed Jan 02 2008 - 09:32:50 CST