DECODE

Compares expression to each search value one by one. If expression is equal to a search, the function returns the corresponding result. If no match is found, the function returns default. If default is omitted, the function returns null.

DECODE is similar to the IF-THEN-ELSE and CASE expressions:

CASE expression
[WHEN search THEN result]
[WHEN search THEN result]
...
[ELSE default];

The arguments can have any data type supported by Vertica. The result types of individual results are promoted to the least common type that can be used to represent all of them. This leads to a character string type, an exact numeric type, an approximate numeric type, or a DATETIME type, where all the various result arguments must be of the same type grouping.

Behavior type

Syntax

DECODE ( expression, search, result [ , search, result ]...[, default ] )

Arguments

expression

The value to compare.

search

The value compared against expression.

result

The value returned, if expression is equal to search.

default

Optional. If no matches are found, DECODE returns default. If default is omitted, then DECODE returns NULL (if no matches are found).

Examples

The following example converts numeric values in the weight column from the product_dimension table to descriptive values in the output.

=> SELECT product_description, DECODE(weight,
      2, 'Light',
     50, 'Medium',
     71, 'Heavy',
     99, 'Call for help',
         'N/A')
  FROM product_dimension
  WHERE category_description = 'Food'
  AND department_description = 'Canned Goods'
  AND sku_number BETWEEN 'SKU-#49750' AND 'SKU-#49999'
  LIMIT 15;
        product_description        |     case
-----------------------------------+---------------
 Brand #499 canned corn            | N/A
 Brand #49900 fruit cocktail       | Medium
 Brand #49837 canned tomatoes      | Heavy
 Brand #49782 canned peaches       | N/A
 Brand #49805 chicken noodle soup  | N/A
 Brand #49944 canned chicken broth | N/A
 Brand #49819 canned chili         | N/A
 Brand #49848 baked beans          | N/A
 Brand #49989 minestrone soup      | N/A
 Brand #49778 canned peaches       | N/A
 Brand #49770 canned peaches       | N/A
 Brand #4977 fruit cocktail        | N/A
 Brand #49933 canned olives        | N/A
 Brand #49750 canned olives        | Call for help
 Brand #49777 canned tomatoes      | N/A
(15 rows)