Oracle+function+DECODE+NULLIF+NVL+NVL2

@Oracle functions detail

@Replace function Oracle

a customer search which shows clients who spent over $1k. The formula is as below.

NVL2((CASE WHEN SUM({transaction.netamountnotax})>1000 Then 1 ELSE null END),SUM{transaction.netamountnotax},NULL)

http://etutorials.org/SQL/Mastering+Oracle+SQL/Chapter+9.+DECODE+and+CASE/9.1+DECODE+NULLIF+NVL+and+NVL2/ Most of Oracle's built-in functions are designed to solve a specific problem. If you need to find the last day of the month containing a particular date, for example, the LAST_DAY function is just the ticket. The DECODE, NULLIF, NVL, and NVL2 functions, however, do not solve a specific problem; rather, they are best described as inline if-then-else statements. These functions are used to make decisions based on data values within a SQL statement without resorting to a procedural language like PL/SQL. Table 9-1 shows the syntax and logic equivalent for each of the four functions.

Table 9-1. If-then-else function logic ||~ Function syntax
 * ~ Logic equivalent ||
 * DECODE(E1, E2, E3, E4) || IF E1 = E2 THEN E3 ELSE E4 ||
 * NULLIF(E1, E2) || IF E1 = E2 THEN NULL ELSE E1 ||
 * NVL(E1, E2) || IF E1 IS NULL THEN E2 ELSE E1 ||
 * NVL2(E1, E2, E3) || IF E1 IS NULL THEN E3 ELSE E2 ||

9.1.1 DECODE
The DECODE function can be thought of as an inline IF statement. DECODE takes three or more expressions as arguments. Each expression can be a column, a literal, a function, or even a subquery. Let's look at a simple example using DECODE: code **SELECT lname,**

**DECODE(manager_emp_id, NULL, 'HEAD HONCHO', 'WORKER BEE') emp_type**


 * FROM employee;**

LNAME               EMP_TYPE

---

SMITH               WORKER BEE

ALLEN               WORKER BEE

WARD                WORKER BEE

JONES               WORKER BEE

MARTIN              WORKER BEE

BLAKE               WORKER BEE

CLARK               WORKER BEE

SCOTT               WORKER BEE

KING                HEAD HONCHO

TURNER              WORKER BEE

ADAMS               WORKER BEE

JAMES               WORKER BEE

FORD                WORKER BEE

MILLER              WORKER BEE code In this example, the first expression is a column, the second is NULL, and the third and fourth expressions are character literals. The intent is to determine whether each employee has a manager by checking whether an employee's column is NULL. The DECODE function in this example compares each row's column (the first expression) to NULL (the second expression). If the result of the comparison is true, DECODE returns  (the third expression); otherwise,  (the last expression) is returned. Since the DECODE function compares two expressions and returns one of two expressions to the caller, it is important that the expression types are identical or that they can at least be translated to be the same type. This example works because E1 can be compared to E2, and E3 and E4 have the same type. If this were not the case, Oracle would raise an exception, as illustrated by the following example: code **SELECT lname,**

**DECODE(manager_emp_id, SYSDATE, 'HEAD HONCHO', 'WORKER BEE') emp_type**


 * FROM employee;**

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER code Since the column, which is numeric, cannot be converted to a DATE type, the Oracle server cannot perform the comparison and must throw an exception. The same exception would be thrown if the two return expressions (E3 and E4) did not have comparable types. The previous example demonstrates the use of a DECODE function with the minimum number of parameters (four). The next example demonstrates how additional sets of parameters may be utilized for more complex logic: code **SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,**

**DECODE(p.status,**

**'INSTOCK', 'In Stock',**

**'DISC', 'Discontinued',**

**'BACKORD', 'Backordered',**

**'ENROUTE', 'Arriving Shortly',**

**'UNAVAIL', 'No Shipment Scheduled',**

**'Unknown') part_status**


 * FROM part p INNER JOIN supplier s**


 * ON p.supplier_id = s.supplier_id;**

PART_NBR        PART_NAME               SUPPLIER            PART_STATUS

--- --- --

AI5-4557        Acme Part AI5-4557      Acme Industries     In Stock

TZ50828         Tilton Part TZ50828     Tilton Enterprises  In Stock

EI-T5-001       Eastern Part EI-T5-001  Eastern Importers   In Stock code This example compares the value of a part's column to each of five values, and, if a match is found, returns the corresponding string. If a match is not found, then the string 'Unknown' is returned. Although the 12 parameters in this example are a great deal more than the 4 parameters of the earlier example, we are still a long way from the maximum allowable parameters, which is 255.

9.1.2 NULLIF
The NULLIF function compares two expressions and returns NULL if the expressions are equivalent, or the first expression otherwise. The equivalent logic using DECODE looks as follows: code DECODE(E1, E2, NULL, E1) code NULLIF is useful if you want to substitute NULL for a column's value, as demonstrated by the next query, which shows salary information for only those employees making less than $2000: code **SELECT fname, lname,**

**NULLIF(salary, GREATEST(2000, salary)) salary**


 * FROM employee;**

FNAME               LNAME                    SALARY

--

JOHN                SMITH                       800

KEVIN               ALLEN                      1600

CYNTHIA             WARD                       1250

TERRY               JONES

KENNETH             MARTIN                     1250

MARION              BLAKE

CAROL               CLARK

DONALD              SCOTT

FRANCIS             KING

MARY                TURNER                     1500

DIANE               ADAMS                      1100

FRED                JAMES                       950

JENNIFER            FORD

BARBARA             MILLER                     1300 code In this example, the GREATEST function returns either the employee's salary or 2000, whichever is greater. The NULLIF function compares this value to the employee's salary and returns NULL if they are the same.

9.1.3 NVL and NVL2
The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE. The following example uses NVL2 to produce the same results as the DECODE example shown in a previous section: code **SELECT lname,**

**NVL2(manager_emp_id, 'WORKER BEE', 'HEAD HONCHO') emp_type**


 * FROM employee;**

LNAME               EMP_TYPE

---

SMITH               WORKER BEE

ALLEN               WORKER BEE

WARD                WORKER BEE

JONES               WORKER BEE

MARTIN              WORKER BEE

BLAKE               WORKER BEE

CLARK               WORKER BEE

SCOTT               WORKER BEE

KING                HEAD HONCHO

TURNER              WORKER BEE

ADAMS               WORKER BEE

JAMES               WORKER BEE

FORD                WORKER BEE

MILLER              WORKER BEE code NVL2 looks at the first expression, in this case. If that expression evaluates to NULL, NVL2 returns the third expression. If the first expression is not NULL, NVL2 returns the second expression. Use NVL2 when you wish to specify alternate values to be returned for the case when an expression is NULL, and also for the case when an expression is not NULL. The NVL function is most commonly used to substitute a default value when a column is NULL. Otherwise, the column value itself is returned. The next example shows the ID of each employee's manager, but substitutes the word '' when no manager has been assigned (i.e., when is NULL): code **SELECT emp.lname employee,**

**NVL(mgr.lname, 'NONE') manager**


 * FROM employee emp LEFT OUTER JOIN employee mgr**


 * ON emp.manager_emp_id = mgr.emp_id;**

EMPLOYEE            MANAGER

--

FORD                JONES

SCOTT               JONES

JAMES               BLAKE

TURNER              BLAKE

MARTIN              BLAKE

WARD                BLAKE

ALLEN               BLAKE

MILLER              CLARK

ADAMS               SCOTT

CLARK               KING

BLAKE               KING

JONES               KING

SMITH               FORD

KING                NONE code Even though DECODE may be substituted for any NVL or NVL2 function, most people prefer to use NVL or NVL2 when checking to see if an expresssion is NULL, presumably because the intent is clearer. Hopefully, the next section will convince you to use CASE expressions whenever you are in need of if-then-else functionality. Then you won't need to worry about which built-in function to use.

6

|| ||
 * [|Mastering Oracle SQL, 2nd Edition] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || [|Preface] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || [|Chapter 1. Introduction to SQL] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || [|Chapter 2. The WHERE Clause] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || [|Chapter 3. Joins] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 4. Group Operations] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 5. Subqueries] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 6. Handling Temporal Data] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 7. Set Operations] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 8. Hierarchical Queries] ||
 * [[image:http://etutorials.org/shared/css/tree/minus.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 9. DECODE and CASE] ||

**Remember the name: eTutorials.**
 * ||  || [[image:http://etutorials.org/shared/css/tree/hr.gif]] ||   ||
 * [[image:http://etutorials.org/shared/css/tree/hr.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|9.2 The Case for CASE] ||
 * [[image:http://etutorials.org/shared/css/tree/hr_l.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|9.3 DECODE and CASE Examples] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 10. Partitioning] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 11. PL/SQL] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 12. Objects and Collections] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 13. Advanced Group Operations] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 14. Advanced Analytic SQL] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 15. SQL Best Practices] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 16. XML] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 17. Regular Expressions] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Chapter 18. Model Queries] ||
 * [[image:http://etutorials.org/shared/css/tree/plusik.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Appendix A. Oracle's Old Join Syntax] ||
 * [[image:http://etutorials.org/shared/css/tree/hr_l.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Colophon] ||
 * [[image:http://etutorials.org/shared/css/tree/hr_l.gif]] || <span style="background-color: transparent; color: #0000ff; font-family: verdana,arial,sans-serif; font-size: 13px; text-decoration: none;">[|Colophon] ||

http://www.cnblogs.com/brainstormlee/archive/2008/07/23/1249528.html

=<span style="color: #935a32; font-family: 黑体,;">[|To be an Software Architect!] =

Life is not fair,get used to it!~
<span style="color: #333333; display: block; font-family: Verdana,Arial,Helvetica,sans-serif; font-size: 12px; text-align: right;">随笔- 4 文章- 0 评论- 0 = [|Nullif、NVL、NVL2、Decode用法] =
 * [|博客园]
 * [|首页]
 * [|新随笔]
 * [|联系]
 * [|管理]
 * [|订阅][[image:http://www.cnblogs.com/images/xml.gif caption="订阅" link="http://www.cnblogs.com/brainstormlee/rss"]]

<span style="display: block; font-family: Verdana,Arial,Helvetica,sans-serif; font-size: 12px;"> NULLIF (expr1, expr2) expr1與expr2相等返回NULL，不等則返回expr1 注意類型要一致

NVL2 (expr1, expr2, expr3) expr1不為NULL，返回expr2；為NULL，則返回expr3

NVL (expr1, expr2) expr1為NULL,則返回expr2. 如果expr1不為NULL,則返回expr1

decode (expression, search_1, result_1, search_2, result_2, ., search_n, result_n, default) decode函數比較表達式和搜索字，如果匹配，返回結果；如果不匹配，返回default值；如果未定義default值，則返回空值.

使用方法： 1、比较大小 select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign函数根据某个值是0、正数还是负数，分别返回0、1、-1 例如： 变量1=10，变量2=20 则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的.

2、表、视图结构转化 现有一个商品销售表sale，表结构为： month　　　 char(6)　　　　　 --月份 sell　　　　number(10,2)　　　--月销售金额

现有数据为： 200001　　1000 200002　　1100 200003　　1200 200004　　1300 200005　　1400 200006　　1500 200007　　1600 200101　　1100 200202　　1200 200301　　1300

想要转化为以下结构的数据： year　　　 char(4)　　　　　 --年份 month1　　number(10,2)　　　--1月销售金额 month2　　number(10,2)　　　--2月销售金额 month3　　number(10,2)　　　--3月销售金额 month4　　number(10,2)　　　--4月销售金额 month5　　number(10,2)　　　--5月销售金额 month6　　number(10,2)　　　--6月销售金额 month7　　number(10,2)　　　--7月销售金额 month8　　number(10,2)　　　--8月销售金额 month9　　number(10,2)　　　--9月销售金额 month10　 number(10,2)　　　--10月销售金额 month11　 number(10,2)　　　--11月销售金额 month12　 number(10,2)　　　--12月销售金额

结构转化的SQL语句为：

create or replace view v_sale( year ,month1,month2,month3,month4,month5,month6,month7,month8 ,month9,month10,month11 ,month12)

as 　　　　 select substrb( month, 1 , 4 ), sum (decode(substrb( month, 5 , 2 ), '01' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '02' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '03' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '04' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '05' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '06' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '07' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '08' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '09' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '10' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '11' ,sell, 0 )), sum (decode(substrb( month, 5 , 2 ), '12' ,sell, 0 )) from sale group by substrb( month, 1 , 4 ); http://www.myexception.cn/database/1307952.html = Oracle中函数运用技巧(1) - Decode = Oracle中函数运用技巧(1) - Decode

Oracle中函数使用技巧(1) - Decode

ecode函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能.

DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能. DECODE有什么用途 呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录 中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断. 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成. 如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else. 初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能.

DECODE(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n，缺省值)

DECODE(字段，比较1，值1，比较2，值2，.....，比较n，值n缺省值)

该函数的含义如下：

IF 条件=值1 THEN

RETURN(翻译值1)

ELSIF 条件=值2 THEN

RETURN(翻译值2)

......

ELSIF 条件=值n THEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

END IF

decode函数使用技巧

·软件环境:

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为:C:/ORANT

· 使用方法:

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如:

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的.

2、表、视图结构转化

现有一个商品销售表sale，表结构为:

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

现有数据为:

200001　　1000

200002　　1100

200003　　1200

200004　　1300

200005　　1400

200006　　1500

200007　　1600

200101　　1100

200202　　1200

200301　　1300

想要转化为以下结构的数据:

year　　　char(4)　　　　　 --年份

month1　　number(10,2)　　　--1月销售金额

month2　　number(10,2)　　　--2月销售金额

month3　　number(10,2)　　　--3月销售金额

month4　　number(10,2)　　　--4月销售金额

month5　　number(10,2)　　　--5月销售金额

month6　　number(10,2)　　　--6月销售金额

month7　　number(10,2)　　　--7月销售金额

month8　　number(10,2)　　　--8月销售金额

month9　　number(10,2)　　　--9月销售金额

month10　　number(10,2)　　　--10月销售金额

month11　　number(10,2)　　　--11月销售金额

month12　　number(10,2)　　　--12月销售金额

结构转化的SQL语句为:

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)),

sum(decode(substrb(month,5,2),'02',sell,0)),

sum(decode(substrb(month,5,2),'03',sell,0)),

sum(decode(substrb(month,5,2),'04',sell,0)),

=
=========================================

补充1：

有学生成绩表student,现在要用decode函数实现以下几个功能：成绩>85,显示优秀；>70显示良好；>60及格；否则是不及格.

假设student的编号为id,成绩为score,那么：

select id, decode(sign(score-85),1,'优秀',0,'优秀',-1,

decode(sign(score-70),1,'良好',0,'良好',-1,

decode(sign(score-60),1,'及格',0,'及格',-1,'不及格')))

from student；

=
=========================================

补充2：

Decode函数的语法结构如下:

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值.

以下是一个简单测试，用于说明Decode函数的用法:

SQL> create table t as select username,default_tablespace,lock_date from dba_users;

Table created.

SQL> select * from t;

USERNAME DEFAULT_TABLESPACE LOCK_DATE

-- -- -

SYS SYSTEM

SYSTEM SYSTEM

OUTLN SYSTEM

CSMIG SYSTEM

SCOTT SYSTEM

EYGLE USERS

DBSNMP SYSTEM

WMSYS SYSTEM 20-OCT-04

8 rows selected. SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;

USERNAME STATUS

--

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS locked

8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked') status from t;

USERNAME STATUS

--

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS

8 rows selected.