SQL editor

Some transformation blocks use SQL scripts e.g. to retrieve data from a database or to perform any update.

tip

The autocompletion function which is activated by the Ctrl+Space key combination is worth using. In the prompting window, there are both the SQL syntax elements and additional elements specific for the Data processing. For more see: Syntax completion

The script content may be supplemented with additional expressions used for referring to data available in the current record or in the entire package.

The content of the scripts can be supplemented with additional expressions for referring to data, available in the current record or package as a whole.

Data references can be implemented in two ways:

As parameter values - then data references should be placed between the strings: #(' and ')# For example, having a structure with a field of type Text named Field1, we can create the following query:

SELECT * FROM TABLE where COLUMN = #(Field1)#

In the above example, the value that will come from the data record formatted into SQL will be inserted in place of the reference to the Field1. So, if we have a data record containing the value "Some text" in the Field1 , the query, depending on the selection of the Parameterized queries option in the Database Reader block, will be in the following form:

If Parameterized queries has the value Yes (parametric query):

SELECT * FROM TABLE where COLUMN = @Field1

If Parameterized queries has the value No:

SELECT * FROM TABLE where COLUMN = 'Some text'

As SQL fragments to create dynamic SQL queries - then data references should be placed between the characters: ##(' and ')# For example, having a structure with a field named Column_name of type Text, we can create the following query:

SELECT ##(Column_name)# as COL FROM TABLE

In this case, in place of the reference to the Column_name field, the value that will come from the data record without any formatting will be inserted. Thus, if we have a data record containing the value "My_column" in the Column_name field, the query will be of the form:

SELECT My_column as COL FROM TABLE

The algorithm for running the processing package requires that the data schemas of all blocks are known before running the package. In the case of "dynamic" SQL queries before running the package, it is not always able to insert a value in place of the data reference that will generate a valid query. This may cause an error at the package validation stage, and thus the package will not be launched. (E.g., for the query SELECT ##(Column_name)# as COL FROM TABLE before running the package, the system will not know what to insert in the Column_name field, so it will try to insert an empty value in this field reference, which will result in an incorrect SQL query). To prevent this, there is an option in place of the data reference to specify a default value to be used at the validation stage. The default value is given in parentheses, after a comma, e.g.:

SELECT ##(Column_name, Default_value)# as COL FROM TABLE

This solution allows you to specify a value during design if the actual values are not available (e.g., ARG not set in the package (package call argument) or no current record).

In the example below, we have a three-letter string ‘S’ to use in a text reference (double ##) and a single letter S as a parameter reference (single #)

SELECT * FROM sys.objects WHERE type in (##(ARG:ARG_ReportList,‘S’)#)
--e.g. (‘S’)

SELECT * FROM sys.objects WHERE type = #(ARG:zz,S)#

Another example, two elements. The “design-time” value is the entire text from the comma to the closing parenthesis.

SELECT * FROM sys.objects WHERE type in (##(ARG:ARG\_ReportList,‘S’, ‘U’)#)

The following data trees can be referenced in the script content:

Query records: #(Field1)# - the value of a field of a record, e.g. a query used as a SQL parameter.
Package arguments: #(ARG:PackageArgumentName)#
Package variables: #(PKG:BlockNameParameterName)#
Dictionaries declared in the package: #(DIC:DictionaryName)#

The editor itself also makes it easier to work with SQL by coloring syntax and explaining elements pointed to with the mouse.

See also​

See also