Re: DECODE

"Sherman H." wrote:
>
> I have to run a query to give a column a value based on a time range. Can I
> use DECODE?
>
> select decode(trans_date, trans_date>='01-Jul-2002' and
> trans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,
> from. . .
> where. . .

You can't use the DECODE statement. The DECODE statement is as follows:

DECODE(value,if1,then1[,if2,then2,]...[,else])

The 'value' can be a column as you have indicated. The value of that column is then compared to the value specified in 'if1'. If those values match, then decode returns 'then1'. If that value matches 'if2', then 'then2' is returned. If no values matched, and if it is specified, then 'else' is returned. The DECODE statement does not do conditional processing as you've indicated in the second paramter.

If you are using Oracle 9i, then you most likely want to implement the CASE function instead of DECODE. If you are pre-9i, then you will most likely want to code this as a stored function and reference the function in your SELECT statement.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"