Re: calculated value in a DECODE statement

On Mon, 17 Mar 2003, mmiller_at_epix.net wrote:

> I have a numeric field that, if it is greater than 0, return
> the field, else return 0.
>
> I've tried ;
> DECODE(numfield, > 0, numfield,0)
> DECODE(numfield, numfield > 0, numfield,0)
> DECODE(numfield, (numfield) > 0, numfield,0)

You need to put them all into one decode block.

I like CASE (just cause I used it in my earlier project)

    ORA>select * from t1;

           FLD

             1
             2
            -1
             0

    ORA>select fld, case when fld > 0
        then fld else 0 end from t1;

           FLD CASEWHENFLD>0THENFLDELSE0END
    ---------- ----------------------------
             1			      1
             2			      2
            -1			      0
             0			      0

The decode version might look like:

    select fld, decode (abs(fld), fld, fld,0) from t1;

           FLD DECODE(ABS(FLD),FLD,FLD,0)

• -------------------------- 1 1 2 2 -1 0 0 0

-- 
Galen Boyer