function in a decode

[pras]

Hi Everybody,

How can i call a function in a decode.If i have to call the function in the else part of the decode will it work.

Any good example will help me.

Thanks in advance

pras

[jmodic]

And what seems to be the problem in using functions inside DECODE?

select job, decode(job, 'PRESIDENT', LOWER(job), INITCAP(job))
from scott.emp;

LOWER() and INITCAP() in the above example are functions used inside the DECODE, and INITCAP() is used in the else part of it.

[pras]

Hi jmodic,

There are some tables say

table1 -- column11,column12

table2 -- column21,column22

table3 -- column31,column32

table4 -- column41,column42,


what i require is

select column11 from table1 where column12 =
decode(column21,'M',(select column31 from table3 where column32=column22),
(select column41 FROM column4 WHERE column21 = column42))

how can i write the above query more efficiently..
i am planning to use the function in the else part...
any advantages of using function...thanks

pras