Netezza DECODE Function Syntax and Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:2 mins read

Netezza supports the DECODE function and it is similar to DECODE function in other relational databases such as Oracle, SQL Server, MySQL, Redshift etc. You can use the Netezza DECODE function to implement if-then-else statement in Netezza nzsql. This command is a short-hand form of Netezza CASE function.

Netezza DECODE Function Syntax

DECODE (expr, expr_val1, ’expr_rep1’, expr_val2, ’expr_rep2’, expr_val3, ’expr_rep3’, ’else-expr’) ;

Execution Flow:

If ‘expr’ is equal to expr_val1 then ’expr_rep1’ is returned, if ‘expr’ is equal to ‘expr_val2’ then ’expr_rep2’ is return, if ‘expr’ is equal to ‘expr_val3’ then ’expr_rep3’ is returned , if nothig is matching then ’else-expr’ will be returned.

Netezza SQL implements the decode function as a variant of a simple case expression, and it is equivalent to the expression “case x when val1 then result 1 when val2 then result 2 else default end.” Except if both x and val1 contain NULL values, unlike a case expression, decode considers the NULL values to be equal.

Netezza DECODE Function Example

Below is the Netezza DECODE Function Example:

SYSTEM.ADMIN(ADMIN)=> select DECODE(cnt,1,'One',2,'Two',3,'Three','None') as counters 
SYSTEM.ADMIN(ADMIN)-> from (select 1 as cnt union all select 2 as cnt union all select 3 union all select 4 as cnt) a; 
 COUNTERS 
---------- 
 One 
 Two 
 Three 
 None 
(4 rows)

Read:

Tags: ,