Re: Help with DECODE

You can use the sign function with decode in this case:

select id, type, decode(sign(5.5-to_number(magnitude)),1,'faint','very faint')
from astro;

(You don't need the "to_number" function if your magnitude column is already numeric.)

The sign function on a numeric value returns 1, -1, or 0 for positive, negative, and zero values. Used above, you can trick it into doing what you need. A number less than 5.5 substracted from 5.5 will return a positive number, the sign of the positive number is 1, and used with decode it will return 'faint', otherwise, it will return 'very faint'.

-- 
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com

Alistair Thomson <alistair.thomson_at_spinxcst.co.uk> wrote in article
<35470AA9.9538B9F5_at_spinxcst.co.uk>...


> 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