Re: Sort in Query

On Jan 2, 10:32 am, Bram Mariën <b...@xxxxxxxxxxxxxxxxx> 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.
.

Loading