Re: Decode function

Hello !

[Quoted] Here is what you want to get :

order by
decode(ITF.ITEM_TYPE,

            (between 60 and 64) , 0,
           (between 30 and 32) ,1,
           45, 2,
           90, 3,
           46, 4,
           5)

And here is a way to get it :

order by
decode(greatest(item_type,64),

            64 /* x<=64 */, decode(least(item_type, 60),
                                                 60 /* 60<=x<=64 */, 0,
                                                 /* x<60 */
                                                 decode(greatest(item_type,32),
                                                              32 /* x<=32 */,

30 /* 30<=x<=32 */, 1,

/* x<30 */ 5),

                                                              /* x>32 */
                                                              45, 2,
                                                              46, 4,
                                                              5)),
           /* x>64 */
           90, 3,
           5)

But you had better to use a function (with some if else ...)

or an external join with a table X like this :

START_VALUE END_VALUE SORT_VALUE

------------------ ---------------- -----------------
30                        32                     1
45                        45                     2
46                        46                     4
60                        64                     0
90                        90                     3

A select clause :

   ITF.ITEM_TYPE (+) between X.START_VALUE and X.END_VALUE

And finally, the sort clause :

order by nvl(X.SORT_VALUE,5)

Bye

Pierre Received on Tue Oct 26 1999 - 14:14:42 CEST