Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle DECODE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 81 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?