Thanks for the question, KISHORE.
Asked: July 05, 2016 - 3:21 pm UTC
Last updated: July 05, 2016 - 4:33 pm UTC
Version: 12C
Viewed 10K+ times! This question is 
You Asked
Hello,
I am trying to use the below SQL :
SELECT least ( DECODE (:VAR1, 9999, NULL, :VAR1), DECODE (:VAR2,9999, NULL,:VAR2) ) FROM DUAL;
VAR1 & VAR2 need to be NUMBERs (not varchar)
the above SQL seems to work for all numbers except for the range of 100 to 149 (positive numbers )
For example :
when VAR1 = 125 and VAR2 = 15
SELECT LEAST ( DECODE (125,9999, NULL,125), DECODE (15,9999, NULL,15) ) FROM DUAL;
the LEAST function yields 125 as the answer !! where as I am expecting 15 as the answer.
the GREATEST function also behaves strangely with the above variables.
Any advice is appreciated.
Thanks
and Chris said...
Beware implicit conversions!
As it says in the docs:
"If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2."
http://docs.oracle.com/database/121/SQLRF/functions057.htm#SQLRF00631 So, because your decode includes a null return value it gives the result as a string!
In character terms, '125' < '15'.
To avoid this, use to_number either around null or the whole decode:
SQL> SELECT LEAST ( DECODE (125,9999, NULL,125),
2 DECODE (15,9999, NULL,15) ) l
3 FROM DUAL;
L
---
125
SQL>
SQL> SELECT LEAST ( DECODE (125,9999, to_number(NULL),125),
2 DECODE (15,9999, to_number(NULL),15) ) l
3 FROM DUAL;
L
----------
15
SQL>
SQL> SELECT LEAST ( to_number(DECODE (125,9999, NULL,125)),
2 to_number(DECODE (15,9999, NULL,15)) ) l
3 FROM DUAL;
L
----------
15
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment