<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    隨筆-26  評論-13  文章-46  trackbacks-0

    In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

    The syntax for the decode function is:

    decode( expression , search , result [, search , result]... [, default] )

    expression is the value to compare.

    search is the value that is compared against expression.

    result is the value returned, if expression is equal to search.

    default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).


    For Example:

    You could use the decode function in an SQL statement as follows:

    SELECT supplier_name,
    decode(supplier_id, 10000, 'IBM',
    10001, 'Microsoft',
    10002, 'Hewlett Packard',
    'Gateway') result
    FROM suppliers;

    The above decode statement is equivalent to the following IF-THEN-ELSE statement:

    IF supplier_id = 10000 THEN
    ???? result := 'IBM';

    ELSIF supplier_id = 10001 THEN
    ??? result := 'Microsoft';

    ELSIF supplier_id = 10002 THEN
    ??? result := 'Hewlett Packard';

    ELSE
    ??? result := 'Gateway';

    END IF;


    The decode function will compare each supplier_id value, one by one.


    Frequently Asked Questions


    Question:? One of our viewers wanted to know how to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1.

    Answer:? To accomplish this, use the decode function as follows:

    decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

    The formula below would equal 0, if date1 is greater than date2:

    (date1 - date2) - abs(date1 - date2)


    Question:? I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

    Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

    For example:

    SELECT supplier_id,
    decode(trunc ((supplier_id - 1) / 10), 0, 'category 1',
    1, 'category 2',
    2, 'category 3',
    'unknown') result
    FROM suppliers;

    In this example, based on the formula:

    trunc ((supplier_id - 1) / 10

    The formula will evaluate to 0, if the supplier_id is between 1 and 10.
    The formula will evaluate to 1, if the supplier_id is between 11 and 20.
    The formula will evaluate to 2, if the supplier_id is between 21 and 30.

    and so on...


    Question:? I need to write a decode statement that will return the following:

    If yrs_of_service < 1 then return 0.04
    If yrs_of_service >= 1 and < 5 then return 0.04
    If yrs_of_service > 5 then return 0.06

    How can I do this?

    Answer:? You will need to create a formula that will evaluate to a single number for each one of your ranges.

    For example:

    SELECT emp_name,
    decode(trunc (( yrs_of_service + 3) / 4), 0, 0.04,
    1, 0.04,
    0.06) as perc_value
    FROM employees;

    Helpful Tip: One of our viewers suggested combining the SIGN function with the DECODE function as follows:

    The date example above could be modified as follows:

    DECODE(SIGN(date1-date2), 1, date2, date1)

    The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses

    DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

    posted on 2006-04-04 12:51 似水流年 閱讀(468) 評論(0)  編輯  收藏 所屬分類: Oracle
    主站蜘蛛池模板: 色播在线永久免费视频网站| 色拍自拍亚洲综合图区| 亚洲免费黄色网址| 最近免费中文字幕大全免费| 亚洲AV天天做在线观看| 久久久久久一品道精品免费看| 亚洲自偷自偷图片| 男人天堂免费视频| 亚洲成AV人片天堂网无码| 国产免费阿v精品视频网址| 国产A在亚洲线播放| 毛片无码免费无码播放 | 亚洲成在人线aⅴ免费毛片| 24小时日本在线www免费的| 亚洲综合小说另类图片动图 | 男人j进女人p免费视频| 亚洲综合另类小说色区色噜噜| 国产精品内射视频免费| 国产成人精品日本亚洲| 24小时免费看片| 亚洲国产综合自在线另类| 一本无码人妻在中文字幕免费| 亚洲av成人一区二区三区观看在线 | 亚洲午夜无码久久久久| 九九精品成人免费国产片| 亚洲最新在线视频| 久久久久国产精品免费免费搜索 | 一级毛片免费不卡| 久久精品国产亚洲沈樵| 成人免费观看一区二区| 性色av极品无码专区亚洲| 亚洲区小说区图片区| 最近2019中文字幕免费直播| 亚洲日韩中文字幕一区| 国产亚洲精午夜久久久久久| 最近免费中文字幕大全高清大全1| 亚洲精品永久在线观看| 77777亚洲午夜久久多人| 免费黄色网址网站| 最近免费mv在线电影| 亚洲AV无码一区二区三区牛牛|