Decode, switch (decode)
Description
Conditional operation that evaluates the values in a specified field, verifies if they match a specified value and transform them into a different value in case they do. You can also set an additional value that will be returned when the values don't match.
This operation is a conditional control flow statement, but unlike the Conditional (ifthenelse) operation, multiple execution paths are allowed.
How does it work in the search window?
Select Create column in the search window toolbar, then select the Decode, switch operation. You must specify at least 3 arguments, and you can add additional arguments as required to create different conditions.
| Argument | Description | Data type |
|---|---|---|
| When (mandatory) | Choose the field whose values you want to evaluate. | Any |
| is (mandatory) | The field or value to be matched with the values in the When argument. | Same as the data type selected in the When argument. |
| transform to (mandatory) | The new column will show this value if the values in When match the values in is. | Any |
| otherwise | The new column will show this value if the values in When don't match the values in is. If you don't add this optional argument, the new column will show null when the values don't match. | Same as the data type selected in the When or transform to arguments. |
After adding an otherwise argument, add a new argument to add another is + transform to set (the otherwise argument will be transformed into an is argument). Click again to add the optional otherwise argument. You can repeat this as required to add all the conditions you need.
The data type of the output values depends on the types selected in the is and otherwise arguments.
Example
In the demo.ecommerce.data table, we want to add a column that shows OK when the statusCode column value is 200, and error for the rest of them (4xx and 5xx codes). To do it, we will create a new column using the Decode, switch operation.
The arguments needed to create the new column are:
- When - statusCode column
- is - Click the pencil icon and enter 200
- transform to - Click the pencil icon and enter OK
- otherwise - Click the pencil icon and enter error
Click Create column and you will see the following result:
How does it work in LINQ?
Use the operator select... as... and add the operation syntax to create the new column. See below the possible formats of the Decode, switch operation.
decode(when_field, is_value, transform_to_value)decode(when_field, is_value, transform_to_value, otherwise_value)decode(when_field, is_value, transform_to_value, is_value2, transform_to_value2...)
Example
You can copy the following LINQ script and try the above example on the demo.ecommerce.data table.
from demo.ecommerce.data
select decode(statusCode, 200, "OK", "error") as statusCode_decode