Re: decode

"deniz" <servermuratk_at_yahoo.com> wrote in message news:FjRjj4b3$GA.212_at_news-02.uni.net...
> can someone point me to examples for decode? is it possible to use where
> clause in conjunction with decode?

Yes. For example, select rows from a table and prompt the user for where-clause criteria, but if it's null (the user didn't enter any criteria), return everything:

select *
from sometable
where somecolumn =
decode('&&prompted_for_value',null,somecolumn,'&&prompted_for_value');

You can also do stuff like join tables with it and simulate an outer join w/o actually doing an outer join:

select 'X'
from table1, table2
where table1.column = table2.column (+);

select 'X'
from table1, table2
where table1.column = decode(table2.column,null,table1.column,table2.column);

I don't have SQL at home to try it but I believe that the each of the above will return the same thing.

-Matt Received on Sat Jun 24 2000 - 00:00:00 CEST