Combining 'search' values in DECODE

[David Stephenson]

Hello list,

When using 'decode' is it possible to combine 2 different search
values into 1

e.g

decode(a.country, 'CH', di.identity,'JA', di.identity, de.identity)

it would be nice to say something like

decode(a.country, 'CH or JA', di.identity, de.identity)

I'm not suggesting that that is the correct syntax, but is it
possible, like it would be in a 'case' statement to give the result
for 2 values, an either or, rather than having to explicitly give the
result for each value. i can't find any examples of this.

This is Oracle 8.1.3, by the way, I know there's something else you
can use instead of DECODE, in Oracle 9.

Many thanks in advance

David

[andrewst]

Originally posted by David Stephenson

Hello list,

When using 'decode' is it possible to combine 2 different search
values into 1

e.g

decode(a.country, 'CH', di.identity,'JA', di.identity, de.identity)

it would be nice to say something like

decode(a.country, 'CH or JA', di.identity, de.identity)

I'm not suggesting that that is the correct syntax, but is it
possible, like it would be in a 'case' statement to give the result
for 2 values, an either or, rather than having to explicitly give the
result for each value. i can't find any examples of this.

This is Oracle 8.1.3, by the way, I know there's something else you
can use instead of DECODE, in Oracle 9.

Many thanks in advance

David

No, with DECODE you have to do it that way.

The "something else" is CASE:

CASE WHEN a.country IN ('CH','JA') THEN di.identity ELSE de.identity END

This works in 8.1.7, but I don't know about 8.1.3.

--
Posted via http://dbforums.com