Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

DECODE

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.

Return Value

The data type of the first result argument.

Syntax

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

Parameters

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 equals search.

default

An expression to return when expression is not equal to search.

Usage 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 equals expr.

Examples

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