Re: Multiple column sorting with a decode

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 24 Nov 2005 17:17:34 -0500
Message-ID: <WNednaeF7ICdoRvenZ2dnUVZ_tudnZ2d@comcast.com>

<voidhack_at_gmail.com> wrote in message
news:1132866433.723828.221930_at_o13g2000cwo.googlegroups.com...
> Thank you for your answer.
> You are right, I did not express myself correctly.
>
> In my example I used column numbers to write the question more quickly,
> but even using column names, it still has the exact same result.
>
> Column 1 is not a constant, I simply used it as an example. What I am
> trying to do is provide the user excuting the report a choice on the
> multiple ordering. If :param =1 for example, then the sorting will be
> on 3 of the columns and if param = 2 on 4 (different) columns (for
> example).
>
> What might work is this: ORDER BY DECODE(...), DECODE(...), DECODE(...)
> providing I can fill the unused ones with something that does not
> change the result.
>
> Once again, thanks for your answer.
>

a SQL (not tool-specific) solution would be:

select ....
from ....
where ..

order by decode( :sort_type

  , 1, cola||colb||colc
  , 2, colb||colc
  , 3, colc||cola

)

or use CASE instead of decode if your version supports it

++ mcs Received on Thu Nov 24 2005 - 16:17:34 CST