Using the DECODE() Function

You use DECODE( value , search_value , result , default_value ) to compare value with search_value . If the values are equal, DECODE() returns result , otherwise default_value is returned. DECODE() allows you to perform if-then-else logic in SQL without having to use PL/SQL.

The following example illustrates the use of DECODE() :

  SELECT DECODE(1, 1, 2, 3)   FROM dual;  DECODE(1,1,2,3) ---------------  2 

In this example, DECODE() returns 2 because 1 is compared to 1 and since they are equal 2 is returned.

The next example uses DECODE() to compare 1 to 2, and since they are not equal 3 is returned:

  SELECT DECODE(1, 2, 1, 3)   FROM dual;  DECODE(1,2,1,3) ---------------  3 

The next example compares the available column in the more_products table. If available equals Y, the string Product is available is returned, otherwise the string Product is not available is returned:

  SELECT prd_id, available,     DECODE(available, 'Y', 'Product is available',     'Product is not available')   FROM more_products;  PRD_ID A DECODE(AVAILABLE,'Y','PR ---------- - ------------------------  1 Y Product is available  2 Y Product is available  3 N Product is not available  4 N Product is not available  5 Y Product is available 

You can pass multiple search and result parameters to DECODE() , as shown in the following example:

  SELECT product_id, product_type_id,   DECODE(product_type_id,   1, 'Book',   2, 'Video',   3, 'DVD',   4, 'CD',   'Magazine')   FROM products;  PRODUCT_ID PRODUCT_TYPE_ID DECODE(P ---------- --------------- --------  1 1 Book  2 1 Book  3 2 Video  4 2 Video  5 2 Video  6 2 Video  7 3 DVD  8 3 DVD  9 4 CD  10 4 CD  11 4 CD  12 Magazine 

Notice that if:

• product_type_id is 1, Book is returned

• product_type_id is 2, Video is returned

• product_type_id is 3, DVD is returned

• product_type_id is 4, CD is returned

• product_type_id is any other value, Magazine is returned