Friday, June 01, 2007

Least, Greatest vs Sign, Decode in Oracle

SELECT DECODE(SIGN(30-40), -1, 30, 40) FROM DUAL;

So, what does this piece of SQL query mean?

SIGN(x) = 1 where x > 0
SIGN(x) = -1 where x < 0
SIGN(x) = 0 where x = 0

So, the above result is 30. Ok, what if 30 is not just 30 and 40 is not just 40, where 30 and 40 are very long expression. What the SQL query will look like? And you need to maintain it. Insane. And really admire previous developer's work. The query is very very long. Then just found out what he/she intend to do.

Ya, you are right, the query is to find which is the Smallest number. So is there any Function can achieve such great work ? Yes, Least and Greatest.

Finally, the query become like this:
SELECT LEAST(30, 40) FROM DUAL;

LEAST(10, 20, 50, 70) = 10
LEAST('10', '2', '3', '7') = 10


Cheers

No comments: