-
Contributing Member
Decode in SQL
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?
-
Expert Member
Re: Decode in SQL
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 .
-
Contributing Member
Re: Decode in SQL
Thank you it was a nice explanation with a detailed example. Hope it helped many people in this discussion
-
Junior Member
Re: Decode in SQL
good explation.. keep it up
but the syntax and the example does not match..plz check.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules