Quick Search:
 
 The Oracle DECODE Function      [Return To Index] Jump to:  

Term: DECODE

Definition:
The DECODE function is analogous to the "IF THEN ELSE" conditional statement. DECODE works with values, columns, and expressions of all data types.

Example Syntax:

DECODE (expression, search, result [, search, result]... [, default])


The DECODE function compares expression against each search value in order. If a match (equality) is found between the expression and the search argument then it returns the corresponding result. If there is no match, the default value is returned (if defined), else it returns NULL. In case of a type compatibility mismatch, Oracle internally does an implicit conversion (if possible) to return the results.

Interestingly, Oracle considers two NULLs to be equivalent while working with the DECODE function.

SQL> SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL;

DECODE
-----
EQUAL


If expression is NULL, then Oracle returns the result of the first search that is also NULL. The maximum number of components in the DECODE function, including expression, searches, results, and default, is 255.

SQL> SELECT EMPLOYEE_NAME, SALARY, DECODE (HIRE_DATE, SYSDATE,'NEW JOINEE','EMPLOYEE')
FROM EMPLOYEES


Related Links:

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org