AX3302

Defining transform statements

For each transform listed on the Transforms tab of the Import Wizard, you must use the Edit Transform dialog to define the associated SQL statement or transform function. To open the dialog, click the ... button in a transform field.

At the top of the Edit Transform dialog, specify whether the transform is a SQL statement, or a Built-in Function. By default, SQL is selected.

Defining a SQL statement transform

If the transform is a SQL statement, complete the following:

Field Description

Enter a SQL statement

Field	Description
Enter a SQL statement	Type or copy and paste the SQL statement into the text editor. To validate the syntax of the SQL statement, click the Check SQL syntax button . NOTE: When referencing Axiom Software tables in a SQL statement, place the table name in curly brackets. For example, to reference the table DEPT, use `{DEPT}`. The SQL statement can use import variables and/or transform variables (that were defined in a previous transform statement). See Using variables in imports. If the statement includes variables, then the ability to check the SQL syntax is not available. Variables can be used in calculations in the SQL statement, for example, to calculate the value of a second variable based on the first variable. NOTE: If the variable defines the destination table, then you must place the variable in double curly brackets when you use it in a SQL statement, so that the eventual table name value is enclosed in curly brackets as expected. For example, if you have a variable named "destinationtable", you would reference that variable as `{{destinationtable}}`. That way, when the `{destinationtable}` value is defined, it will resolve as `{GL2019}`. For some examples of common SQL transforms, see Example SQL statements for transforms.
Target Variable Name	If you want to define a target variable for the transform, type the name of the variable. The SQL statement can be any query that results in a single value. If the query returns multiple values, the target variable uses the first value in the return set. The target variable can be used in subsequent transforms, or as destination columns in the Mapping tab. To use the variable in the import, enter the variable name into one of the supported areas of the import settings, enclosed in curly brackets {}. For example, if the variable name is Column, you would enter `{column}`. Transform variable names cannot use the same name as import variable names, and vice versa. Every variable name within the import must be unique. Transform variables should not use the same name as an Axiom Software table that you plan to reference in the import, because the syntax for table names is the same as for variables (both are placed in curly brackets). If a variable name and a table share the same name, any references will be interpreted as the variable, not the table.
Description	If desired, type a description for the transform. The description may be useful when viewing the list of transforms on the Transforms tab, to easily identify specific transforms.

Type or copy and paste the SQL statement into the text editor. To validate the syntax of the SQL statement, click the Check SQL syntax button .

NOTE: When referencing Axiom Software tables in a SQL statement, place the table name in curly brackets. For example, to reference the table DEPT, use {DEPT}.

The SQL statement can use import variables and/or transform variables (that were defined in a previous transform statement). See Using variables in imports. If the statement includes variables, then the ability to check the SQL syntax is not available.

Variables can be used in calculations in the SQL statement, for example, to calculate the value of a second variable based on the first variable.

NOTE: If the variable defines the destination table, then you must place the variable in double curly brackets when you use it in a SQL statement, so that the eventual table name value is enclosed in curly brackets as expected. For example, if you have a variable named "destinationtable", you would reference that variable as {{destinationtable}}. That way, when the {destinationtable} value is defined, it will resolve as {GL2019}.

For some examples of common SQL transforms, see Example SQL statements for transforms.

Target Variable Name

If you want to define a target variable for the transform, type the name of the variable.

The SQL statement can be any query that results in a single value. If the query returns multiple values, the target variable uses the first value in the return set.

The target variable can be used in subsequent transforms, or as destination columns in the Mapping tab. To use the variable in the import, enter the variable name into one of the supported areas of the import settings, enclosed in curly brackets {}. For example, if the variable name is Column, you would enter {column}.

Transform variable names cannot use the same name as import variable names, and vice versa. Every variable name within the import must be unique.

Transform variables should not use the same name as an Axiom Software table that you plan to reference in the import, because the syntax for table names is the same as for variables (both are placed in curly brackets). If a variable name and a table share the same name, any references will be interpreted as the variable, not the table.

Description

If desired, type a description for the transform.

The description may be useful when viewing the list of transforms on the Transforms tab, to easily identify specific transforms.

Using a transform function

If the transform is a built-in function, select the function that you want to use from the Function list, and then complete the parameters for the function.

You can edit the description for the transform if desired. By default, the standard description of the function is used.

NOTE: If you select a different function, the current description will be overwritten with the standard description of the new function. If you go back to the original function before saving the transform, the original description will be preserved.