Re: Sort in Query

On Jan 2, 10: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...

Trying to do greater than and less than comparisons against a character field is a danger signal regardless of whether the SQL also includes order by. To me at least these types of comparisons belong to the field of mathematics and if they are going to be used in an application properly then then should be supported by a numeric column in the database.

There are times when it might be possible to get away with them against character data if the character data is controlled very carefully. However your allowing a mix of numbers in the character data along with other non-numbers is another big warning signal.

I would recommend thinking very carefully about the design and consider making your column invoice number numeric or at the very least consistent in format all the time.

A real kludge and not recommended would be to add another column in the table and use some kind of trigger based logic to maintain a consistent formatted invoice number. This "hidden column" could be if necessary referenced in SQL as needed to get results that are acceptable. Received on Thu Jan 03 2008 - 09:28:15 CST