DECODE

The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.

Tip:

To change the maximum size for new segments in an existing workspace, use the AW command with the SEGMENTSIZE keyword. To discover the current maximum size for new segments, use the AW function with the SEGMENTSIZE keyword.

Return Value

The data type of the first result argument.

Syntax

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

Arguments

expr: The expression to be searched. The function automatically converts expr to the data type of the first search value before comparing
search: An expression to search for. The function automatically each search value to the data type of the first search value before comparing
result: The expression to return when expression is equal to search.
default: An expression to return when expression is not equal to search.

Notes

Order of Value Evaluation

The search, result, and default values can be derived from expressions. The function evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, the function never evaluates a search when a previous search is equal to expr.

Examples

Example 12-5 Decoding an ID Field

Assume that your analytic workspace contains the following objects. Note that the inventory_location formula uses the DECODE function to identify text values that correspond to the INTEGER values of warehouse_id.

DESCRIBE
DEFINE product_id DIMENSION TEXT
DEFINE warehouse_id DIMENSION INTEGER
DEFINE inventories VARIABLE DECIMAL <product_id warehouse_id>
DEFINE inventory_location FORMULA TEXT <warehouse_id>
EQ -
DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 4, 'Seattle', -
'Non domestic')
 
REPORT inventories
                     ---------------INVENTORIES---------------
                     ---------------PRODUCT_ID----------------
WAREHOUSE_ID                 1770                 1775
-------------------- -------------------- --------------------
                   1                30.63                79.02
                   2                71.49                55.83
                   3                88.71                68.02
                   4                86.27                41.86
 
 
REPORT inventory_location
WAREHOUSE_ID          INVENTORY_LOCATION
-------------------- --------------------
                   1 Southlake
                   2 San Francisco
                   3 Non domestic
                   4 Seattle

The following reports illustrate how you can use inventory_location to display the decoded values of warehouse_id in a report rather than displaying the actual values of warehouse_id.

LIMIT product_id to '1775'
 
REPORT DOWN warehouse_id inventories
                     ----INVENTORIES-----
                     -----PRODUCT_ID-----
WAREHOUSE_ID                 1775
-------------------- --------------------
                   1                79.02
                   2                55.83
                   3                68.02
                   4                41.86
 
REPORT DOWN inventory_location inventories
                     ----INVENTORIES-----
                     -----PRODUCT_ID-----
INVENTORY_LOCATION           1775
-------------------- --------------------
Southlake                           79.02
San Francisco                       55.83
Non domestic                        68.02
Seattle                             41.86

Example 12-6 DECODE with BITAND

Assume that you have the following objects with the reported values within your analytic workspace.

DEFINE order_id DIMENSION TEXT
DEFINE customer_id DIMENSION TEXT
DEFINE order_customer COMPOSITE <order_id customer_id>
DEFINE order_status VARIABLE NUMBER(2) <order_customer<order_id customer_id>>
 
REPORT DOWN order_customer order_status
  ORDER_ID   CUSTOMER_ID  ORDER_STATUS
------------ ------------ ------------
2458         101                  0.00
2397         102                  1.00
2454         103                  1.00
2354         104                  0.00
2358         105                  2.00
2381         106                  3.00
2440         107                  3.00
2357         108                  5.00
2394         109                  5.00
2435         144                  6.00
2455         145                  7.00
2356         105                  5.00
2360         107                  4.00

Assume that order_status is used as a bitmap where the first three bits hold information about the order and the other bits are always 0:

The first bit is used for location information:

0 = Post Office. This corresponds to INTEGER values of 0, 2, 4, and 6.

1 = Warehouse. This corresponds to the numerical values of 1, 3, 5, and 7.
The second bit is used for method:

0 = Air. This corresponds to the numerical values of 0, 1, 4, and 5.

1 = Ground. This corresponds to the numerical values of 2, 3, 6, and 7.
The third bit is used for receipt:

0 = Certified. This corresponds the numerical values of 0, 1, 2, and 3.

1 =Insured. This corresponds to the numerical values of 4, 5, 6, and 7.

The following formulas use DECODE to substitute the text values for the bit values.

DEFINE location FORMULA DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
DEFINE method FORMULA DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air')
DEFINE receipt FORMULA DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified')

Now, you can issue a report to display the decoded values.

REPORT DOWN order_customer order_status location method receipt

  ORDER_ID   CUSTOMER_ID  ORDER_STATUS   LOCATION      METHOD      RECEIPT
------------ ------------ ------------ ------------ ------------ ------------
2458         101                  0.00 PostOffice   Air          Certified
2397         102                  1.00 Warehouse    Air          Certified
2454         103                  1.00 Warehouse    Air          Certified
2354         104                  0.00 PostOffice   Air          Certified
2358         105                  2.00 PostOffice   Ground       Certified
2381         106                  3.00 Warehouse    Ground       Certified
2440         107                  3.00 Warehouse    Ground       Certified
2357         108                  5.00 Warehouse    Air          Insured
2394         109                  5.00 Warehouse    Air          Insured
2435         144                  6.00 PostOffice   Ground       Insured
2455         145                  7.00 Warehouse    Ground       Insured
2356         105                  5.00 Warehouse    Air          Insured
2360         107                  4.00 PostOffice   Air          Insured