DB2 DECODE函数的用法


DB2 DECODE函数的用法
 
在查询数据，需要进行条件判断时，一般我们使用CASE...WHEN实现，当判断条件为相等时，除了使用CASE...WHEN实现，还可以使用DECODE函数。若要使用like、>、<等其他判断条件时，就只能使用CASE...WHEN实现了。下面就解释下DECODE()函数的用法。
DECODE()使用方法： 
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
  www.zzzyk.com  
DECODE()含义说明：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
  www.zzzyk.com  
附上DBCODE（）官方说明
：
DECODE scalar function
 
                           .--------------------------------------.                              
                           V                                      |                              
>>-DECODE--(--expression1----,--expression2--,--result-expression-+--+--------------------+--)-><
                                                                     '-,--else-expression-'      
 
The schema is SYSIBM.
The DECODE function compares each expression2 to expression1. If expression1 is equal to expression2, or both expression1 andexpression2 are null, the value of the following result-expresssion is returned. If no expression2 matches expression1, the value ofelse-expression is returned; otherwise a null value is returned.
The DECODE function is similar to the CASE expression except for the handling of null values:
A null value of expression1 will match a corresponding null value of expression2.
If the NULL keyword is used as an argument in the DECODE function, it must be cast to an appropriate data type.
The rules for determining the result type of a DECODE expression are based on the corresponding CASE expression.
Examples:
The DECODE expression:
   DECODE (c1, 7, 'a', 6, 'b', 'c')
achieves the same result as the following CASE expression:
   CASE c1
     WHEN 7 THEN 'a'
     WHEN 6 THEN 'b'
     ELSE 'c'
   END
Similarly, the DECODE expression:
   DECODE (c1, var1, 'a', var2, 'b')
where the values of c1, var1, and var2 could be null values, achieves the same result as the following CASE expression:
   CASE
     WHEN c1 = var1 OR (c1 IS NULL AND var1 IS NULL) THEN 'a'
     WHEN c1 = var2 OR (c1 IS NULL AND var2 IS NULL) THEN 'b'
     ELSE NULL
   END
Consider also the following query:
   SELECT ID, DECODE(STATUS, 'A', 'Accepted',
                             'D', 'Denied',
                             CAST(NULL AS VARCHAR(1)), 'Unknown',
                             'Other')
   FROM CONTRACTS
Here is the same statement using a CASE expression:
   SELECT ID,
     CASE
       WHEN STATUS = 'A' THEN 'Accepted'
       WHEN STATUS = 'D' THEN 'Denied'
       WHEN STATUS IS NULL THEN 'Unknown'
       ELSE 'Other'
     END
   FROM CONTRACTS