The DECODE function compares one expression to one or more other expressions and, when the base expression equals 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.
The expression to be searched. The function automatically converts expr to the data type of the first search value before comparing
An expression to search for. The function automatically each search value to the data type of the first search value before comparing
The expression to return when expression equals search.
An expression to return when expression is not equal to search.
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 equals expr.
Example 7-64 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 7-65 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 the value of 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, which corresponds to integer values of 0, 2, 4, and 6.
1 = Warehouse, which corresponds to the integer values of 1, 3, 5, and 7.
The second bit is used for method:
0 = Air, which corresponds to the integer values of 0, 1, 4, and 5.
1 = Ground, which corresponds to the integer values of 2, 3, 6, and 7.
The third bit is used for receipt:
0 = Certified, which corresponds the integer values of 0, 1, 2, and 3.
1 =Insured, which corresponds to the integer 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