<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
    主站蜘蛛池模板: 久操免费在线观看| xxxxwww免费| 午夜一级免费视频| 国产亚洲sss在线播放| 亚洲黄色免费在线观看| 久久久久亚洲AV无码麻豆| 美女视频黄a视频全免费网站色窝| 亚洲人成网站18禁止一区| www免费黄色网| 久久亚洲一区二区| 无码人妻久久一区二区三区免费| 亚洲Aⅴ无码专区在线观看q| 一区二区三区免费视频网站| 久久久久国产亚洲AV麻豆| 亚洲一卡一卡二新区无人区| 国产成人免费网站在线观看| 亚洲熟妇无码八V在线播放| 最好免费观看韩国+日本| 国产精品亚洲а∨无码播放麻豆| 免费一级做a爰片久久毛片潮喷| 一级一级毛片免费播放| 国产成人精品日本亚洲专区61| 男人进去女人爽免费视频国产| 亚洲综合一区二区精品久久| 一二三四影视在线看片免费 | 亚洲AV日韩AV无码污污网站| 日韩人妻无码免费视频一区二区三区| 黄色a三级三级三级免费看| 青青草原亚洲视频| 中文字幕无码播放免费| 国产精品亚洲综合天堂夜夜| 亚洲精品成人片在线观看精品字幕| 久久久久久久99精品免费| 亚洲精品国产国语| 亚洲精品国产综合久久一线| 无码人妻精品中文字幕免费| 国产精品亚洲精品久久精品| 亚洲va久久久噜噜噜久久天堂| 免费无码黄十八禁网站在线观看| 无码 免费 国产在线观看91| 亚洲春黄在线观看|