Re: Sort in Query

On Jan 2, 11:15 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 2, 9: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...
>
> 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- Hide quoted text -
>
> - Show quoted text -

To add to David's questions: how is the invoice number stored in the database. One varchar2 column with slash included? Multiple columns and data created via concatenation?

What kind of table: heap or IOT, was used to hold the data in Oracle? Is the data in a SQL Server cluster table with invoice as the cluster key?

You really want all invoice values to include a slash in the same relative positon so that by normallizing the preceeding and following digits into n digits formats the data would sort into what appears to be your desired ordering.

HTH -- Mark D Powell -- Received on Wed Jan 02 2008 - 11:28:33 CST