Help with DECODE

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 Received on Wed Apr 29 1998 - 06:10:33 CDT