Decode Function

DECODE

Syntax

Purpose

DECODE compares expr to each search value one by one.

If expr is equal to a search,

Then Oracle Database returns the corresponding result.

If no match is found, then

Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_

DOUBLE) or character types.

The maximum number of components in the DECODE function, including expr,

Searches, results, and default, is 255.

DECODE ( expr , search , result

,

, default

)

Test Case

Create table plch_employees (

   Id    integer primary key

, name varchar2 (40)

, gender char (1)       

)

/

Insert into plch_employees values (100, 'Mr. John Doe','M')

/

Insert into plch_employees values (200, 'Mrs. Jane Doe','F')

/

Insert into plch_employees values (300, 'Ms. Julie Doe','F')

/

Insert into plch_employees values (400, 'Mr. Jack Doe','M')

/

Insert into plch_employees values (500, 'Dr. James Doe','M')

/

Insert into plch_employees values (600, 'Jonathan Doe','M')

/

Insert into plch_employees values (700, 'Jeff Jr. Doe','M')

/

Commit

/

select id, name, decode(gender, 'M', 'MALE', 'F', 'FEMALE', gender)

  from plch_employees;

Test Case:

/*

User have requirement ( if parameter value is 100 then query only show the result of those employee who have id no 100

Else query will show all employees

Expect 100 no ID)

*/

---we can handle it from our decode function 

Select id, name, and decode (gender, ‘M’, MALE, ‘F’, ‘FEMALE’, gender)

  From plch_employees

  Where decode (id, 100,'Y','F') =decode (:v_id, 100,'Y','F');