using case, Valid statement?

[Michael Hill]

Is this a valid sql statement:

SELECT
case field1
when 'first' then 1
when 'second' then 1
else null
end
FROM mytable

Mike

[Michael Hill]

Re: using case, Valid statement?

I can use decode like:

decode(
field1,'first', 1,
decode(field1,' second',2,0
)
)

except that this has to be all nested and is very hard to work with. If
I have nine items it becomes very ugly.

Michael Hill wrote:

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

[Michael Hill]

Re: using case, Valid statement?

I can use decode like:

decode(
field1,'first', 1,
decode(field1,' second',2,0
)
)

except that this has to be all nested and is very hard to work with. If
I have nine items it becomes very ugly.

Michael Hill wrote:

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

[kibeha]

Re: using case, Valid statement?

Michael Hill <hillmw@ram.lmt as.lmco.comwrot e in message news:<4034E917. 248B00CF@ram.lm tas.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