What is the purpose or usage of decode function in SQL. Someone brief me on the syntax also. If you could give an example for this function it would help me?

Here is syntax of DECODE function in Oracle

DECODE(expr,search1,result1[,search2,result2]...[,default_val]);

DECODE compares expr to each search(search1,search2...) value one by one. If expr is equal to a search1, then Oracle returns result1 else it compares expr to search2 and so on. If no match is found, then Oracle returns default_val.

the above decode function works exactly like the following if... else if...else statement.

if (expr = search1) then
return result1
else if expr= search2) then
return result2
...
else
return default_val;


Example:

SELECT product_id,
DECODE (id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non-domestic')
Location FROM inventris
WHERE prod_id < 175;


This example decodes the value warehouse_id. If id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non-domestic'.



I hope this helps .

Thank you it was a nice explanation with a detailed example. Hope it helped many people in this discussion