Using Expressions in Column Mapping

Knowledge Best Practices
1

Using Expressions in Column Mapping

Within the Rivery platform, in a data source to target river, and nearly any other instance where there is auto-mapping or column mapping involved, there is an option to create custom columns in the “expressions” box on the very right, as can be seen in the image below:

image
image1519×768 59.6 KB

This powerful functionality allows the user to customize their output table beyond the limits of the raw data extracted from the source by including expressions, mathematical or string operations, or simple functions to enrich and tailor the output to their use-case.

What expressions can go into the “expression” column?

The expressions that can go into this column are any ones that perform a selection, condition, or mathematical operation on another column (Generally, anything that could go after a SELECT statement in a simple query. In addition, depending on the syntax of your target DB, some basic functions are supported, most notably, any ones that access a current datetime.

For example, the following example queries are all valid statements that can be executed in the “expressions” box:

<column_name>

<column_name> * 100

CASE (WHEN <column_name> = 1 THEN True ELSE False) END

CASE (
    WHEN (<column_name> = 'IsAverage') THEN AVG(<column_name_2>) 
    WHEN (<column_name> = 'IsSum') THEN SUM(<column_name_2>)
    ELSE 'No Aggregation')
END

CURRENT_TIMESTAMP()

What statements can’t go into the “expressions” box?

Not all statements can be executed from within the “expressions” box. Any statements that modify the granularity of the table (JOIN’s, GROUP BY’s, built in functions, ALTER TABLE’s, CREATE TABLE’s etc.) are not supported.

If you desire to create a column that results from more complex transformations (joins, groupings, schema modification etc.) it is recommended to create a Logic River and perform the transformations there.

2 Likes