Does Oracle have an IIF Function?

[AaronA]

The subject should explain my question, to reiterate I have a field say FieldA that is 1's and 0's (True or False)

AN Example
Select IIf(FieldA = 0,'Yes','No') From TableName;

I have tried using double quotes too. I always get this error:

ORA-00907: missing right parenthesis, and it points to the equals sign in the test parameter. I decided to try a C style equality test ( == ) but that didnt work either.

Anyway, this should be a quick one. Thanks!

--Aaron

 

[lewisp]

SELECT DECODE(FieldA,0,'Yes','No') FROM table;

The decode is not limited to single return values:

SELECT DECODE(field,0,'Yes',1,'Maybe','No') FROM table;

In this example if field = 0 then Yes, if field = 1 then Maybe otherwise if field = anything else then No.

 

[jee]

There is also a CASE construct which can do some of the same things as decode (and perhaps is easier to read):

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END) from emp e;

SELECT CASE WHEN FieldA = 0 THEN 'Yes'
ELSE 'No'
END
FROM table;


Limitations under 8i: Can't use in PL/SQL unless you are using dynamic SQL.

 

[AaronA]

Thanks for the help, both of you!

--Aaron

 

[KhushiRaina]

hi,
I am using case in one of my query, its working fine but now i want to group on this case, how can i do this??
Eg:- SELECT CASE WHEN FieldA = 0 THEN 'Yes'
ELSE 'No'
END case1
FROM table
group by case1;
This wld give an error.
Anyother way i can group on case

Khushi.

 

[DaPi]

Khushi, try using DECODE as above.

 

[RVLF]

Warning with CASE ...

It's does'nt work in PL/SQL ....