The Expression Editor in Matillion ETL allows the user create expressions in the Calculate and Join components. From the editor, users can browse from lists of available functions (dependent on platform) and data columns and use them to write and store their own expressions.
For JOIN expressions, the list of expression is fixed, one per join. You may selected which expression to edit but not add or remove expressions.
For Calculate, you may add and remove as many expressions as required. See "Add a new expression".
You may refer to previous expressions within the current expression by using its name, enclosed in single quotes.
Click the [+] to add a new expression, then provide a name and expression for it. Existing expressions can be removed with [-].
Available input columns are listed. Double-clicking them will add them to the expression editor at the current cursor position, or you can drag-and-drop them into the editor. Note that if the list is empty, it is likely because the input component is currently not in a valid state. Re-validate your job and try again.
The name of the current expression can be whatever you like. Although it is a column name, it does get quoted - this means that you may use spaces and other special characters, providing that they are all ASCII characters and the total length does not exceed 127 characters.
The expression is a valid SQL expression to compute values for the expression. For JOINS this is should be a boolean expression which determines whether rows are considered to match for the purpose of the join. For calculate, any expression is valid. The shortcuts below the editor, the list of columns and the list of functions can help you build up the expressions without having to type everything.
Since this is SQL, references to columns should be enclosed in double-quotes whereas character and date constants should be enclosed in single quotes. Numeric constants should not be quoted.
The function list is provided to help you find the relevant functions you need. Once selected, brief help is displayed along with a link to the full documentation. Double-clicking a function will insert a template for that function call into the editor at the current cursor position.
Note: The list of available functions is platform-dependent. Functions of the same name may work differently depending on the platform. Documentation links will lead to the platform-specific documentation.