Re: Sort in Query

On Jan 3, 8:55 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 3, 3:13 am, Bram Mariën <b..._at_nospam.abiware.be> wrote:
>
>
> > 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
>
> You need to look at an ASCII table and see the character codes
> involved and the 'logic' may then reveal itself to you; as defined a
> '/' is a decimal 047, and a '0' is a decimal 048.  A '/', therefore,
> will always sort BEFORE a '0' in a text field (remember you're now
> using character strings, not numeric values, for your invoice
> number).   That you give the users 'free choice' in the format of the
> invoice number is ... ridiculous, in my opinion.  The orgainzation
> should have a standard policy/format for invoice numbering and
> everyone should be required to adhere to it.  The missing logic is in
> why some users add the slash to the invoice number and others do not,
> thus making a mess of your data.
>
> A standard invoice number format should be implemented and the
> application changed (if possible) to prevent such 'embellishments'
> from occurring.  You then need to clean up your data to remove (or
> add, as the case may be) your meddlesome '/'.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I agree that your application should require/use a standard formatting for the invoice number.

One thing you might consider is to sort the results of a user function on the invoice_no. The function would apply standard formatting to the invoice value and then this standardized result would be what the sort is on.

HTH -- Mark D Powell -- Received on Thu Jan 03 2008 - 08:52:09 CST