Re: Sort in Query

On Wed, 02 Jan 2008 09:28:33 -0800, Mark D Powell wrote:

Hello,

First of all : thanks for those who already responded!

>
> 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?

No, the invoice number is just 1 column (INV_NR), which is VARCHAR2(15).

>
> 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?

This is starting to get chinese for me ... I've looked in the Enterprise Manager Console, and I consulted the 'details' for the specific table, and I see it's a 'Standard table', not a 'Organized Using Index (IOT)' table.
INV_NR is not the primary key, but I did index the column (just a plain create index without any specific params) but that didn't solve anything.

>
> 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.

We calculate a unique invoice number, but the users are free to edit this calculated invoice number.
We've had multiple situations where there were invoice numbers with and without slashes. It's their free choice.

I wouldn't mind if the invoice numbers without slashes all came after those with slashes, but this isn't the case. I just can't find any logic in this sorting thing...

Bram Received on Thu Jan 03 2008 - 03:13:28 CST