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