SQL Query Designer

The SQL Query Designer is used to design a custom SQL query to pull information from a database. It can be opened by clicking the Custom SQL button on the Settings pane, under Input Data.

  • Tables: Lists all tables and stored queries in the database.

  • Custom Query: Displays the query that retrieves information from a database. You may use variables and properties in the query, to make the selection dynamic. See Using variables and properties in an SQL query.
    Each database type has their own version of the SQL query language. To learn how to build your own query, please refer to your database's user manual.

  • Test Query button : Click to test the custom query to ensure it will retrieve the appropriate information.

  • Results: Displays the result of the SQL query when clicking on Test Query.

Using variables and properties in an SQL query

When you use variables and properties in an SQL query, the selection will be dynamically adjusted each time the data mapping configuration is actually used in a Workflow process.

To create a dynamic SQL query:

  • The query must start with =

  • Any variable or property must be enclosed in curly brackets: { ... }. This effectively inserts a JavaScript statement in the query. Note that all other curly brackets must be escaped with a backslash.

Inside the brackets you may enter any of the following property fields defined in the Preprocessor step (see Fixed automation properties and Properties):

  • Fixed automation properties. These are retrieved via the automation object (see automation), for exampleautomation.jobInfo.JobInfo9 or automation.properties.OriginalFilename.

  • Properties that have their scope set to "Entire data". These are retrieved via the data object (see data), for example:data.properties.myProperty.

  • Properties that have their scope set to "Automation variable". These are retrieved via automation.variables(see automation), for example automation.variables.FieldList.

Properties that have their scope set to "Each record" and can not be used because the SQL query is executed before any record is created. For the same reason, variables declared in other Steps can not be used.

Example

= SELECT {automation.variables.FieldList} FROM {automation.jobInfo.JobInfo9}

If the Workflow variable defined as FieldList contains the value "id,name" and Job Info 9 contains the value "MyTable", then this custom query, once parsed, yields the following SQL statement:
SELECT id,name FROM MyTable
which is then executed.