DECODE Function

Simple Code

 SELECT DECODE (value, <if this value>, <return this value>)
FROM dual;

Example Code 

 SELECT program_id,
  DECODE(customer_id, 'AAL', 'American Airlines') AIRLINE,
  delivered_date
FROM airplanes
WHERE ROWNUM < 11;

=> customer_id 값이 'AAL' 값과 동일하면 'American Airlines' 값을 리턴한다.

Complex Code

 SELECT DECODE (value,<if this value>,<return this value>,
                     < if this value>,<return this value>,
                      ....)
FROM dual;

Example Code 

 SELECT program_id,
       DECODE(customer_id,
              'AAL', 'American Airlines',
              'ILC', 'Intl. Leasing Corp.',
              'NWO', 'Northwest Orient',
              'SAL', 'Southwest Airlines',
              'SWA', 'Sweptwing Airlines',
              'USAF', 'U.S. Air Force') AIRLINE,
       delivered_date
FROM airplanes
WHERE ROWNUM < 11

Default Value Code

 SELECT program_id,
       DECODE(customer_id,
             'AAL', 'American Airlines',
             'ILC', 'Intl. Leasing Corp.',
             'NWO', 'Northwest Orient',
             'SAL', 'Southwest Airlines',
             'SWA', 'Sweptwing Airlines',
             'USAF', 'United States Air Force',
             'Not Known') AIRLINE,
       delivered_date
FROM airplanes
WHERE ROWNUM < 11;

Example

 SELECT program_id,
       COUNT (AMERICAN) AAL,
       COUNT (DELTA) DAL,
       COUNT (NORTHWEST) NWO,
       COUNT(INTL_LEASING) ILC
FROM (
   SELECT program_id,
          DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
          DECODE(customer_id, 'DAL', 'DAL') DELTA,
          DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
          DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
   FROM airplanes)
GROUP BY program_id;