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.
Starting with the Incorta Cloud 2022.1.0 Release, 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:
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 . |
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 THENresult := 'Electronics';ELSIF SALES.SALES.PROD_ID = 18 THENresult := 'Hardware';ELSIF SALES.SALES.PROD_ID = 136 THENresult := 'Photo';ELSEresult := '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:
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”
- Replace
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.