Sort in Query
Date: Wed, 02 Jan 2008 16:32:50 +0100
Oracle 9i 126.96.36.199.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) :
(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
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