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).
Applies To:
- Oracle 9i, Oracle 10g, Oracle 11g
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)
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')
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; |
Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".
Answer: Yes, the maximum number of components that you can have in a decode function is 255. This includes the expression, search, and result arguments.
分享到:
相关推荐
AMD Fluid Motion Video是一项AMD研发,将帧率为24FPS【即帧每秒,Frames per Second】或者其他帧率低于60FPS的视频补帧成60帧的...Fixed error on the PotPlayer when enabling decode function using external decoder
博客:PostgreSQL的学习心得和知识总结(四十四)|语法级自上而下完美实现Oracle数据库DECODE函数的实现方案(GreenPlum & AntDB)
base64decode 编码,解码。
A obscure bug was found by HuangYeJun from china, in the RetrieveHeaders function if the retrieved text was larger than 1024 bytes and the crlf.crlf fall in the middle of two chunks, the function is ...
jwt JS解密文件 var getUserjwt = function (token) { if (!token) { return; } } //解析jwt令牌 var jwtDecodeVal = jwt_decode(token);
用js实现的base64encode,base64decode函数. 包括: function base64encode(str) { function base64decode(str) { function utf16to8(str) { function utf8to16(str) { function doit() {
最佳(测试版) python元启发式优化库。 易于扩展和使用。 警告:最佳状态为Beta。 API可能会更改。...# A decode function is useful for converting the binary solution to real numbers def dec
全志 a10开发板 专用的 video 硬件 decoding 源码 video decoding sample source code using allwinner a10 hardware encoding function for development board (h264, h263, mp4,...)
即: 如果有空格就用 代替,如果有其它字符就用%ASCII...具体实现如下: 代码如下:function urldecode(encodestr) newstr=”” havechar=false lastchar=”” for i=1 to len(encodestr) char_c=mid(encode
php也需要配置环境变量 解决Fatal error Call to undefined function curl_init()
代码如下:‘================================================ ‘函数名:URLDecode ‘作 用:URL解码 ‘================================================ Function URLDecode(ByVal urlcode) Dim start,final,...
上传二维码解析,长按识别二维码,生存二维码,reqrcode.js等功能。二维码解析是否成功返回路径,代码直接可以运行
function long Base64Decode(ref blob ouput, ref string input ,long inputlen)library "base64" alias for "Base64Decode;Ansi" 调用实例 string ls_filename,ls_ret long num blob lb_file blob{10000000}...
中文字符编码研究系列第五期,详解 urlencode()与urldecode()函数字符编码原理,两个函数分别用于编码 URL 字符串和解码已编码的 URL 字符串,实现对中文字符的编码
which means no need to write encode or decode function for model support dictionary ->model, see in NSObject+JKCoding support quick cache model data and save data to local disk which means,in ios mvc ...
将Url进行编码,前台JS需要使用这段内容,这时候就需要解码了: 代码 代码如下: /** * Url编码 **/ ShengUtils.encode = function(unzipStr){ var zipstr=””; var strSpecial=”!\”#$%&'()*+,/:;<=>?[]^`{|}~%”...
但是有个问题 虽然JS解码JSON非常方便,但是编码似乎没有什么好办法… 本着能懒即懒,不能懒也尽量懒的原则,古狗了一下 还真让我发现了 PHP里直接用json_decode就可以解码,用起来相当方便 代码如下: function json...
Pitch analyser function.
Function to add noise
本文实例讲述了php自定义urlencode,urldecode函数。分享给大家供大家参考。具体如下: //配合JavaScript的ajaxObject函数, 对字串进行转码. function ajax_encode($str){ ...function ajax_decode($str){ $