Re: Oracle 8i SQL Statement

Originally posted by Kent Prokopy

> When using the DECODE function, is there any way of passing it the

> IN() clause?

>

> Example:

> DECODE(FIELDNAME,IN(1,2,3),TRUE,FALSE)

>

> This returnning TRUE if the values of 1, 2 or 3 are found. So far the

> only way I can get this to work is as follows:

>

> DECODE(FIELDNAME,1,TRUE,2,TRUE,3,TRUE,FALSE) "This leaving a lot of

> room for typo errors."

>

> Thank you.

> Kent Prokopy

Not with DECODE, no. But in 8i we have CASE:

CASE WHEN fieldname IN (1,2,3) THEN 'TRUE' ELSE 'FALSE' END

Note:

1. You can't use boolean TRUE and FALSE in SQL, and DECODE only works in SQL
2. CASE syntax is NOT supported by PL/SQL in 8i (it is in 9i), only in SQL. If your SQL statement is inside a PL/SQL program, you would have to use dynamic SQL to be able to use CASE.

--
Posted via http://dbforums.com