Re: Sort in Query

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 Received on Thu Jan 03 2008 - 07:55:47 CST