Re: If clause in SELECT statements

From: Bastiaan Schaap <bschaap_at_desyde.nl>
Date: 2000/07/19
Message-ID: <newscache$3h0yxf$aeh$1@lnews.actcom.co.il>#1/1

You can also nest the decode function, so you'll get a if-then-else structure:
For example we have a table which holds information about an item, and it's datatype. If we hold the datatype as a single character, we don't want the user to see the character but a nice little description: SELECT i.name,

               decode( i.datatype, 'S', 'String',
                  decode( i.datatype, 'N', 'Number',
                     decode( i.datatype, 'D', 'Date', 'Unknown' ))) AS

data_type
FROM my_items i
ORDER BY i.name;

This would give you a nice query result.

HTH, Bastiaan Schaap
Oracle web development,
Desyde BV - Baarn
http://www.desyde.nl/
tel. +31355411711

Hakan <heren_at_home.com> wrote in message news:3974B827.7359204B_at_home.com...
> hi,
>
> you can use decode function.
>
> select decode(state, 'NY', 'New York', state) as state_desc from
> state_tab
>
> Hakan
>
> Tim Gifford wrote:
> >
> > Is there any way to evaluate a field in a SELECT query w/o writting a
 User
> > Function? This is an example of the logic that I would like to have in
 a
> > view:
> > select if([state]='NY','New York',[state]) as state_desc from
 state_tab
> >
> > If the state column is NY then it would output New York, otherwise, it
 would
> > return the value from the field.
> >
> > Any Ideas,
> > Tim Gifford
> > tgifford(at)horizonstech.com
Received on Wed Jul 19 2000 - 00:00:00 CDT