Decode function in oracle is similar to if else statement.
Syntax: DECODE( expression , search , result [, search , result]... [, default] )
Expression: The value to compare. Data type is automatically converted into first search value before comparing.
Search : The value which is compared against Expression.
Result: The value which is returned if the expression matches with search values.
Default: If not match is found, default value is returned. If default is not specified then null is returned.e.g
select decode(1,1,'ONE',2,'TWO') from dual; Output: ONE
select decode(2,1,'ONE','2',2) from dual; Output : 2
Below throws ORA-01722:
invalid number error as 'TWO' cannot be converted into Number.
select decode(2,1,'ONE','TWO',2) from dual;
Search, result and default values can be derived from expression.Oracle uses short-circut evaluation which means database evaluates search value only before comparing it to expression. Database does not evaluate all search values before comparing any of them with expression.
select decode(2,'ONE','ONE','TWO',2,'No match') from dual; Output : No matchReason:
Expr : 2 is implicitly converted into Character '2' . It does not matches with 'TWO' hence result is No match.
Decode considers 2 Null value to be equivalent.
select decode(NULL,null,'EQUAL','NOT EQUAL') from dual; Output: EQUAL
Decode can also be used in Group by and Order by clause. Decode can also be used for greater than or less than comparison. Stay tuned for more information on decode.
Jay on Sep 21, 2019 at 12:03 am
This article is contributed by Jay. If you like dEexams.com and would like to contribute, you can write your article here or mail your article to firstname.lastname@example.org . See your article appearing on the dEexams.com main page and help others to learn.