	AllExperts > Oracle
	Search

Volunteer
Answers to thousands of questions

Home · More Oracle Questions · Answer Library

· Encyclopedia ·

Oracle - ABOUT DECODE AND SIGN in a single query.

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

Ask a Question