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.gselect 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.
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 admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.
Search
Search
