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 ] )
Parameters
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). |
Example
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)