Results 1 to 4 of 4

Thread: Decode in SQL

  1. #1
    Contributing Member
    Join Date
    May 2006
    Answers
    82

    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?


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    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 .


  3. #3
    Contributing Member
    Join Date
    Jul 2006
    Answers
    76

    Thumbs up Re: Decode in SQL

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


  4. #4
    Junior Member
    Join Date
    Dec 2010
    Answers
    1

    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
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact