Decode, switch (decode)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

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

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