Usage of the DB2 DECODE Function

The usage of the DB2 DECODE function generally uses CASE when data is queried and conditional judgment is required... WHEN the condition is equal, in addition to the CASE... you can also use the DECODE function. If you want to use like,>, <and other judgment conditions, you can only use CASE... WHEN. The following describes how to use the DECODE () function. DECODE () usage: decode (condition, value 1, translation value 1, value 2, translation value 2 ,... value n, translation value n, default value) www.2cto.com DECODE () Meaning Description: IF condition = value 1 then return (translation value 1) ELSIF condition = value 2 then return (translation value 2 )...... ELSIF condition = value n then return (translation value n) else return (default value) end if www.2cto.com attach DBCODE () official description: 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 t 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' ENDSimilarly, the DECODE expression: DECODE (c1, var1, 'A', var2, 'B ') where the values of c1, var1, and var2 cocould 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 ENDConsider also the following query: SELECT ID, DECODE (STATUS, 'A', 'accessted', 'D', 'denied ', CAST (null as varchar (1), 'unknon', 'other ') FROM CONTRACTSHere 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