IIF IN ORACLE

[Eitan]

In sql-server (or access) there is a db-function : IIF.
i.e.
IIF(A=B, <RESULT WHEN TRUE>, <RESULT WHEN FALSE>);

(It should be in a select statemant !!!)

What is the equivalent in Oracle 9i.
(not decode, because decode is one value, like case statement,
I want to ask something like :
if a between 1..20 then
<when true>
else
<when false>
end if;

[Volker Hetzer]

Post by Eitan
In sql-server (or access) there is a db-function : IIF.
i.e.
IIF(A=B, <RESULT WHEN TRUE>, <RESULT WHEN FALSE>);
(It should be in a select statemant !!!)
What is the equivalent in Oracle 9i.
(not decode, because decode is one value, like case statement,
if a between 1..20 then
<when true>
else
<when false>
end if;

http://www.oracle-base.com/articles/9i/Case9i.php#SCE

Lots of Greetings!
Volker

[Mark D Powell]

Besides Volker's reference to the CASE statement there is also the
older DECODE function. Both CASE and DECODE can be used in the SELECT
list and in the WHERE cause. You should probably choose to use CASE
instead of DECODE but older code will have decode in it.

where values for fld1 are 1, 2, and 3.

1* select decode(fld2,1,'ONE',2,'TWO','UNKNOWN') from marktest
UT1 > /

DECODE(
-------
ONE
TWO
UNKNOWN

You can nest both CASE and DECODE statements. See the SQL Manual.

HTH -- Mark D Powell --

[Malcolm Dew-Jones]

Mark D Powell (***@eds.com) wrote:
: Besides Volker's reference to the CASE statement there is also the
: older DECODE function.

It may not be clear how this is useful if you wish things like
IF A between 1 and 10...

for the OP, the usual trick is to use SIGN and some simple arithmetic

decode( sign(a-1),
-1 , return_value_when_a_less_than_1 ,
decode( sign(a-10),
1 , return_value_when_a_more_than_10
return_value_when_a_between_1_and_10
)
)

$0.10