Re: own order by

Why not do the translation in the order by clause?

For example to get X first and F second:

select <field>
from <table>
order by translate(<field>, 'XF', 'AB')

Regards, Andy

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
Received on Tue Jul 09 2002 - 03:54:22 CDT