程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 DECODE函數的用法

DB2 DECODE函數的用法

編輯:DB2教程

DB2 DECODE函數的用法   在查詢數據,需要進行條件判斷時,一般我們使用CASE...WHEN實現,當判斷條件為相等時,除了使用CASE...WHEN實現,還可以使用DECODE函數。若要使用like、>、<等其他判斷條件時,就只能使用CASE...WHEN實現了。下面就解釋下DECODE()函數的用法。 DECODE()使用方法:  decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)   www.2cto.com   DECODE()含義說明: IF 條件=值1 THEN     RETURN(翻譯值1) ELSIF 條件=值2 THEN     RETURN(翻譯值2)     ...... ELSIF 條件=值n THEN     RETURN(翻譯值n) ELSE     RETURN(缺省值) END IF   www.2cto.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  

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved