Re: using case, Valid statement?

Michael Hill <hillmw_at_ram.lmtas.lmco.com> wrote in message news:<4034E917.248B00CF_at_ram.lmtas.lmco.com>...
> I can use decode like:
>
> decode(
> field1,'first',1,
> decode(field1,'second',2,0
> )
> )

You don't have to nest :

decode(field1,

       'first',1,
       'second',2,
       'third',3,
       null)

It can go up to 255 arguments, I think?

> > Is this a valid sql statement:
> >
> > SELECT
> > case field1
> > when 'first' then 1
> > when 'second' then 1
> > else null
> > end
> > FROM mytable

In 9i it is valid syntax.
In 8i you can use :

case

   when field1='first' then 1
   when field1='second' then 2
   else null
end

The good thing about this syntax is, that each when clause can have widely different boolean expressions.

In short:

In 8i you use decode syntax if it's just a case of different values for a column, and use case syntax for more complicated expressions.

In 9i you use the same constructs, but they added the simplified case syntax (the one you ask if it's valid) as an alternative to decode.

/KiBeHa Received on Fri Feb 20 2004 - 02:56:53 CST