Re: own order by

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D29E53E.286D42A5_at_exesolutions.com>...
> Wolf wrote:
>
> > Is there a way to tell oracle how the "order by" clause orders
> > the database entries. (except asc, desc).
> > Example:
> > I want to sort VARCHAR2 Fields but no by alphabetical order.
> > So I want to define that X is the first letter and F ist the second letter
> > in order and so on...
> >
> > Wolf
>
> The best way, of which I am aware, is to create another field and use an
> after-insert and after-update trigger to load it with the sort order
> information using TRANSLATE. Thus if you datafield contains XF the hidden
> field would be loaded with AB. Then, when reporting, do order by based on the
> hidden field.
>
> Daniel Morgan

Where the potential performance benefit of having a pre-translated order column is not required or possible in the case of preexisting data you can order by a user function result where the function translates the input to a new value for sorting purposes. But Daniel's method would be beneficial when this re-ordering would be needed on a regular basis as you would pay the function overhead price once on insert instead of on every row retrieved.

HTH -- Mark D Powell -- Received on Tue Jul 09 2002 - 08:23:51 CDT