Expert: Elliot Mak - 6/10/2009

Question
Please explain me what does this query do exactly:

SUM( DECODE(SIGN(ic.f_i_soh_rglr_qty
                       +ic.f_i_soh_clrc_qty
                       +ic.f_i_soh_prmtn_qty),-1,1,0,1,1,0))   Oos_count


Answer
Good day Raghav

SIGN function returns three values.  They are -1, 0, and 1.  

If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.

Therefore, if ic.f_i_soh_rglr_qty+ic.f_i_soh_clrc_qty+ic.f_i_soh_prmtn_qty is a negative number,then it will set to 1.  If it is a zero, then it will set to 1, and if it is a positive number, it will set to 0.  

Then it will sum up the results and return a value.  

Hope it helps.  If you have additional question, please let me know

Cheers
Elliot