Equivalent of iif function of MS-Access in Oracle

[naresh]

I have a query here which is stored in a table as
which is basically a Ms-Access query.

select partno from table_name where
iif(variable_1='mercury',hiredate='1-jan-99' or matl_name='steel',matl_name='iron');

Explaination:

select partno from table_name where
if variable1='mercury' then
hiredate='1-jan-99' or matl_name='steel'
else
matl_name='iron'
end if;

note: the value of variable1 I'll be substituting at runtime and matl_name and hiredate are fields of the table.

can we write a similar query in Oracle using decode function which should appear in the "WHERE" clause.

thanx in advance
Naresh

[sig][/sig]

 

[mmontesano]

naresh-
Let me restate what I think you are asking:
[ul]
[li]Test variable1 and if its value is 'mercury' and the hiredate is 1-jan-99 or the matl_name is 'steel' include the record.[/li]
[li]If variable1 isn't 'mercury' and the matl_name is 'iron' include the record.[/li]
[li]If neither case is true exclude the record.[/li]
[/ul]

If this is what you mean, then the following nested DECODE in the WHERE clause will accomplish this. If this is not what you mean, let us know.

select partno 
from table_name 
where 'OK' = 
DECODE(:variable1,
       'mercury', DECODE(hiredate,
                         TO_DATE('1-JAN-99','dd-mon-yy'), 'OK', --include
                         DECODE(matl_name,
                                'steel', 'OK', --include
                                'BAD') -- don't include
                         ),
       DECODE(matl_name,
              'iron', 'OK', --include
              'BAD') -- don't include

[sig][/sig]