Thanks for the question, BHS.
Asked: October 12, 2017 - 8:31 am UTC
Last updated: October 12, 2017 - 9:44 am UTC
Version: 12c
Viewed 1000+ times
You Asked
1. select decode('a',1,1,2) from dual --------> O/P Error saying Invalid Number
2. select decode(1,'a',1,2) from dual --------> O/P : 2
what is the difference in the above two cases and also please explain the execution process?
Thanks In Advance.
with LiveSQL Test Case:
and Chris said...
As the docs say:
Oracle automatically converts expr and each search value to the data type of the first search value before comparing http://docs.oracle.com/database/122/SQLRF/DECODE.htm#SQLRF00631 The first search value is the second argument.
So in the first example it sees the value 1. So the database tries to convert 'a' to a number. Which, unsurprisingly, fails.
But in the second example, you have a character ('a'). You can convert all numbers to characters. So it works!