Built-in Functions → decode

decode() is a conditional statement that enables you to add the procedural if-then-else logic to a query. decode() compares an expression against the search value. If the field value matches the caseValue, then the function returns the thenValue or else it returns the elseValue.

You can compare multiple values against the field value and return a thenValue for each matching result. The decode() statement will compare each field value, one by one.

Signature

decode(field, caseValue, thenValue, ..., elseValue)

The following table illustrates the decode() conditional statement parameters:

Parameter	Description
field	The input field value to compare.
caseValue	The value compared against the `field` value.
thenValue	The value returned as a result for matching codes, if the `field` value is equal to `caseValue`.
elseValue	The value returned as a result for non-matching codes, if the `field` value is not equal to `caseValue`.

Note

To compare multiple values, use the following decode() statement: decode(field, caseValue, thenValue, caseValue, thenValue, elseValue).

Returns

string, int, double, long, date, or timestamp representing the thenValue and elseValue in the column.

Example

This example maps the Product ID: 16 with “Electronics”, 18 with “Hardware”, 136 with “Photo”, and maps all other Product ID values with "Other”.

decode(
   SALES.SALES.PROD_ID,
   16,
   "Electronics",
   18,
   "Hardware",
   136,
   "Photo",
   "Other"
)

The above decode() statement is equivalent to the following if-then-else statement:

IF SALES.SALES.PROD_ID = 16 THEN
   result := 'Electronics';
 
ELSIF SALES.SALES.PROD_ID = 18 THEN
   result := 'Hardware';
 
ELSIF SALES.SALES.PROD_ID = 136 THEN
   result := 'Photo';
 
ELSE
   result := 'Other';
 
END IF;

The following table illustrates the output of the decode() query:

Product	Product ID	decode()
17” LCD w/built-in HDTV Tuner	14	Other
Y Box	16	Electronics
Envoy Ambassador	18	Hardware
1.44 MB External 3.5” Diskette	31	Other
64MB Memory Card	136	Photo

Use the following steps for detailed instructions on how to use the decode() conditional statement:

Note

In the Cluster Management Console (CMC), create a tenant that includes Sample Data. The Sample Data includes the SALES schema.

In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
In the Action bar, select + (add icon), or select + Add Insight.
In the Insight panel, select Listing Table or V.
In Tables, select Pivot Table.
In the Data panel, select Manage Dataset.
In the Manage Data Sets panel, in Tables, select SALES. Close the panel.
From the Data panel, drag and drop the following columns to the respective tray:
- From the PRODUCTS table, drag and drop Product to the Row tray.
- From the SALES table, drag and drop Product Id to the Row tray.
- From the SALES table, drag and drop Year to the Column tray
- From the SALES table, drag and drop Revenue to the Measure tray. In the Properties panel,
  - For Format, select Dollar Rounded.
- From the SALES table, drag and drop Product Id to the Individual Filter tray. In the Filter panel,
  - For Operator, select In.
  - For Values, select 14, 16, 18, 31, and 136.
From the Data panel, drag and drop Add Formula to the Row tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Conditional Statements.
- Double-click the decode function, decode(field, caseValue, thenValue, elseValue), to add the formula to the editor.
- In the Formula Editor,
  - Replace field with Product Id from the Data panel
  - Replace caseValue with 16
  - Replace thenValue with “Electronics”
  - Add two more caseValue, thenValue pairs: 18, "Hardware", 136, "Photo"
  - Replace elseValue with “Other”

decode(SALES.SALES.PROD_ID, 16, "Electronics", 18, "Hardware", 136, "Photo", "Other")

Select Validate & Save.
In the Row tray, double-click the New Formula pill and rename it to decode().
Name the insight Products.
In the Action bar, select Save.

Content

Signature

Returns

Example