Re: Help with DECODE

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Sat, 02 May 1998 20:48:54 GMT
Message-ID: <354b86b0.9940379@www.sigov.si>

On Sat, 02 May 1998 18:20:16 GMT, eric_at_deptj.demon.co.uk (Eric Junkermann) wrote:

>On Wed, 29 Apr 1998 11:41:31 GMT, tim.hall_at_spam.begone (Tim Hall)
>wrote:
>
>>
>>Decode (A,B,C,D) means IF A=B THEN C ELSE D. In other words, it only
>>compares equality, not ranges.
>>
>>What you need to do in circumstances like this is use the GREATEST and
>>LEAST functions in the decode:
>>
>>DECODE(LEAST(magnitude,5.5),5.5,'Very Faint','Faint')
>>
>>This will return the string 'Faint' in magnitude is less than 5.5
>>
>>
>
>But since two boundary points are needed, you will have to say
>
>DECODE(LEAST(magnitude,8.4),8.4,'Very Faint',
> DECODE(LEAST(magnitude,5.5),5.5,'Faint','Visible'))
>
>which means (this is just pseudo-code)
>
> IF 8.4 <= magnitude
> 'Very Faint'
> ELSE
> IF 5.5 <= magnitude
> 'Faint'
> ELSE
> 'Visible'
> ENDIF
> ENDIF
>
>You can go on nesting like this for as many layers as you want, but it
>gets hard to read!

Actualy nesting of DECODE functions can usualy be avoided when you want to mimic the IF-ELSIF-ELSIF-...-ENDIF construction, so that you make it more like CASE construct. For the above example, you can do it if you use SIGN function instead of LEAST and if you use somewhat unusual sequence of DECODE parameters:

SELECT id, type,

       DECODE(1,SIGN(5.5-magnitude),'Visible',    
                SIGN(8.4-magnitude),'Faint','Very Faint') Visibility
FROM astro;
                

This "nesting" of IF expressions can go forever and only one DECODE function must be used.

Usualy DECODE is used so that its first parameter is an expression, and you then assign output values to all of its possible (discrete) result values. But in the above example, the logic is reversed: the first parameter is an expected expression result, which is followed by various expressions and their corresponding output values. The first expression that returns the expected result (equal to the first parameter) will return the resulted output value.

>Eric

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat May 02 1998 - 15:48:54 CDT