DECODE Statement

[CrystalVis]

Can you please tell me if I can use <, >, or = operator in a DECODE statement. Example:

DECODE(tbl.Payment > 0, tbl.Payment, NULL)

or

DECODE(tbl.Payment, tbl.Payment > 0, tbl.Payment, NULL)

DECODE(tbl.Payment, > 0 tbl.Payment, NULL)

None of these statement work!!! I understand the DECODE statement but can't seems to get the stored proc to compile because of the DECODE statement.

DECODE(expr, cond1, val1, cond2, val2...def)
by the way, the database is Oracle 8. Your help is greatly appreciated.

 

[jimbopalmer]

You need actual values to decode, try DECODE(SIGN(tbl.Payment), 1, tbl.payment,NULL)

You may find more help if one of the Oracle Database threads, rather than the ERP thread I tried to remain child-like, all I acheived was childish.

 

[aruproyc]

If you want to compare only with 0 then use sign function, otherwise you can write a user defined database function to compare with a particular value and use in the select statement.

 

[Zemede]

If you want to compare, for instance, tbl.Payment with 200,
use this code:
decode(sign(tbl.Payment -200),0,'two hundred',1,'Greater than 200','less than 200').
This is a nice shortcut rather than writing the procedure.
The value of sign(tbl.Payment -200) is 0 if tbl.Payment = 200.
The value of sign(tbl.Payment -200) is 1 if tbl.Payment > 200.
otherwise, or
The value of sign(tbl.Payment -200) is -1 if tbl.Payment < 200.