​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
 Using numeric comparisons in DECODE

NB This tip is mostly for users of older versions of Oracle (ie pre v8) since the case statement now makes it somewhat redundant. However for large data sets it may be worth trying both just in case the decode method outperforms the case method

Have you ever wished you could do something like the following in SQL?

SELECT num_val,DECODE(num_val,num_val>100,'Greater than 100','Less than or equal to 100') result
FROM mytab

And get back a result set like the one shown below.


Table 1
​num_val result
============
100 Less than or equal to 100
-34  Less than or equal to 100
389  Greater than 100

​Of course, in the syntax shown the SQL statement is incorrect and Oracle will return an error message if you try and run it.However there is a way around this problem by using the little known SIGN and ABS functions. First a quick recap of what the SIGN and ABS functions do. SIGN takes a numeric argument and returns one of the following:

1 if the argument is > 0
0 if the argument = 0
-1 if the argument is < 0

​ABS takes a numeric value and returns its positive value or 0 if the argument is 0.Therefore it follows that the expression ABS(SIGN(X)) where X is any numeric value can only ever return either a 1 or 0. So how does that help us with the above problem? Well, it all involves a little arithmetic but the upshot of it all is that by using the SIGN and ABS functions in conjunction it's possible to construct an equivalent expression for any numeric comparison operation. In fact we'll take this slightly further and develop equivalent expressions that will return a 1 if the comparison is true or return a 0 if false.

​Although this extra step is not always necessary it can be useful because it often results in simpler SQL statements and, as we'll see later, is handy when checking for values contained within a range.

Constructing equivalent expressions

The best way to see how we construct equivalent expressions for numeric comparisons is to have a look at a couple of examples. For instance, consider the simple comparison X = 0, how can we map this using the SIGN and ABS functions so that the equivalent expression will return a 1 if true or a 0 otherwise? Let's break it down into the various steps.

1. SIGN (X) - this expression can only return -1, 0 or 1
2. ABS(SIGN(X)) - this expression can only return 0 (X = 0) or 1 (X != 0)
3. 1 - ABS(SIGN(X)) - this expression can only return 1 (X = 0) or 0 (X != 0)

​For a slightly a more complex example consider the expression X > Y. Breaking it down again we see that:

SIGN(X-Y) - returns 1 if X>Y, otherwise 0 or -1
1 - SIGN(X-Y) - returns 0 if X>Y, otherwise -1 or -2
SIGN(1-SIGN(X-Y)) - returns 0 if true, otherwise -1
1- SIGN((1-SIGN(X-Y))) - returns 1 if true otherwise 0

Now you can do the rest of the math if you want to for other comparison operations but I'll simply present the results here as shown below.

​Remember, if the numeric comparison on the left is true, the equivalent expression on the right will return the value 1 otherwise it will return the value 0.

​Numeric Eq
Table 2

Comparison Expression
===================
X>=0  SIGN(SIGN(X)+1)
X<=0  SIGN(1-SIGN(X))
X>0 (1-SIGN(1-SIGN(X)))
X<0  (1-SIGN(SIGN(X)+1))
X=0  (1-ABS(SIGN(X)))
X >=Y  SIGN(SIGN(X-Y)+1)
X<=Y  SIGN(1-SIGN(X-Y))
​X>Y  (1-SIGN(1-SIGN(X-Y)))

X<Y              (1-SIGN(SIGN(X-Y)+1))
X=Y              (1-ABS(SIGN(X-Y)))
X!=Y             ABS(SIGN(X-Y))

Now, recalling our original SQL statement, to achieve the output desired as shown in Table 1 the SQL now becomes:

SELECT
num_val,
DECODE((1-SIGN(1-SIGN(num_val - 100))),1

'Greater than 100','Less than or equal to 100') result
FROM mytab


Dealing with values in a range


Hopefully you can see that the above arguments can be extended very simply to allow dealing with values contained within a range. Suppose we wanted to have our original select statement produce the following set of results instead:



Table 3


num_val result

===========

100 Between 100 and 200
-34 Less than 100 or greater than 200
389 Less than 100 or greater than 200 


Our pseudo SQL statement to produce this would be:



SELECT num_val,
DECODE(num_val,num_val>100 and num_val < 200 ,
'Between 100 and 200','Less than 100 or greater than 200') result
FROM mytab


From Table 2, the expressions we require are for X > Y and X < Z where Y is 100 and Z is 200. We also know that both of these expressions will return the value 1 if true therefore for both expressions to be true the values added together must equal 2 and this is what we test for in our DECODE statement. Thus our required SQL statement now becomes:



SELECT num_val,DECODE(((1-SIGN(1-SIGN(num_val - 100)) + (1-SIGN(SIGN(num_val-200)+1)),2,'Between 100 and 200',
'Less than or equal to 100 or greater than or equal to 200') result
FROM mytab


Dealing with NULL values


You should be aware that if the argument to the SIGN or ABS function is null it returns a null value. The discussion above assumed we were dealing with non-null arguments only but your code can easily be modified depending on how you want to deal with nulls. Suppose you wanted to modify the SQL statement in our first example so that it produced the output shown in the result table 4 below. As you can see all we need to do is add another expression to our DECODE to deal with the null value



Table 4


num_val result

============
NULL Value is null

100 Less than or equal to 100
-34 Less than or equal to 100
389 Greater than 100



Your SQL statement would become


SELECT
DECODE(num_val,NULL,'NULL',num_val),
DECODE((1-SIGN(1-SIGN(num_val - 100))),1,'Greater than 100',
NULL, 'Value is null',
'Less than or equal to 100') result
FROM mytab