Re: Help with DECODE

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

On Wed, 29 Apr 1998 12:10:33 +0100, Alistair Thomson <alistair.thomson_at_spinxcst.co.uk> wrote:

>Hi
>
>I'm trying to get to grips with decode and am having difficulty in using
>expressions in decode. An example of the data is shown below:
>
>M28 Cluster 6.8
>M29 Cluster 7.1
>M30 Cluster 7.2
>M31 Galaxy 3.4
>M32 Galaxy 8.1
>M33 Galaxy 5.7
>M34 Cluster 5.5
>M35 Cluster 5.3
>M36 Cluster 6.3
>M37 Cluster 6.2
>M38 Cluster 6.4
>M39 Cluster 5.2
>M40 Double Star 8.4
>
>When I use decode like this
>
>select id, type, decode(magnitude,5.5,'faint',8.4,'very faint') from
>astro;
>
>I get the following:
>
>M28 Cluster 6.8
>M29 Cluster 7.1
>M30 Cluster 7.2
>M31 Galaxy 3.4
>M32 Galaxy 8.1
>M33 Galaxy 5.7
>M34 Cluster 5.5 faint
>M35 Cluster 5.3
>M36 Cluster 6.3
>M37 Cluster 6.2
>M38 Cluster 6.4
>M39 Cluster 5.2
>M40 Double Star 8.4 very faint
>
>What I really want to do is say that everything < 5 is visible and >= 5
>is not visible. But when the decode looks like this I get an error:
>
>select id, type, decode(magnitude,<5.5,'faint',>=5.5,'very faint') from
>astro;
>
>Can anyone tell me if it's possible to use decode in this way and if it
>is give a pointer as to how its done.
>
>Thanks
>
>Alistair Thomson
>
>please email alistair.thomson_at_sphinxcst.co.uk
>

Tim Hall, Indus International (was TSW International) tim.hall (at) iint.com
http://www.indusworld.com

Replace domain name with the iint.com before replying via email! Received on Wed Apr 29 1998 - 06:41:31 CDT