Re: Decode function

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 26 Oct 1999 20:15:14 GMT
Message-ID: <381908cd.6924267_at_news.siol.net>

On Tue, 26 Oct 1999 14:14:42 +0200, Pierre Charpenay <pcharpenay_at_unilog.fr> wrote:

>Hello !
>
>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 */,
>decode(least(item_type,30),
>
>30 /* 30<=x<=32 */, 1,
>
>/* x<30 */ 5),
> /* x>32 */
> 45, 2,
> 46, 4,
> 5)),
> /* x>64 */
> 90, 3,
> 5)

I believe the above monster works correctly, however I don't think anybody (except maybe the author immediately after he/she wrote it down) could easily understand or maintain such SQL code. Nested DECODEs always put me in a bad moode as they are so hard to follow and yet they can almost always be avoided.

I believe the following piece of SQL code is much more readable and understandable:

SELECT * FROM itf

ORDER BY DECODE(0, SIGN(46-item_type),     4,  -- item_type = 46
                   SIGN(90-item_type),     3,  -- item_type = 90
                   SIGN(45-item_type),     2,  -- item_type = 45
                   SIGN(item_type-30)+1,   5,  -- item_type < 30
                   SIGN(64-item_type)+1,   5,  -- item_type > 64
                   SIGN(32-item_type+1)-1, 1,  -- 30<=item_type<=32
                   SIGN(item_type+1-60)-1, 0,  -- 60<=item_type<=64
                   5)                          -- all other values

/

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Oct 26 1999 - 22:15:14 CEST