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.

The decode() conditional statement supports the date and timestamp as parameters and return data types.

Signature

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

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

ParameterDescription
fieldThe input field value to compare.
caseValueThe value compared against the field value.
thenValueThe value returned as a result for matching codes, if the field value is equal to caseValue.
elseValueThe 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:

ProductProduct IDdecode()
17” LCD w/built-in HDTV Tuner14Other
Y Box16Electronics
Envoy Ambassador18Hardware
1.44 MB External 3.5” Diskette31Other
64MB Memory Card136Photo

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.