JobRouter variables, form fields and process & subtable fields can be used for writing SQL commands.
JobRouter variables
The name of the variable muss be enclosed in square brackets. A complete list of all available JobRouter system variables can be found in the chapter System variables.
SELECT * FROM TABELLE1 WHERE BENUTZERNAME = [jr_username]
Form fields
When using form fields the name of the form elements must be enclosed in square brackets.
SELECT * FROM TABELLE1 WHERE GEBUCHT = [gebucht]
If you want to refer to a column from a subtable view, the following notation is generally required: [<subtableViewName>_<columnName>]
SELECT DISTINCT mimetype FROM JRFILES WHERE mimetype LIKE '%[subtableViewFileDetails_mimetype]%'
Please note: If the property Disabled is set for a dialogue element, the value is neither transmitted nor evaluated. This applies in particular to the sending of a step as well as the evaluation and resolution in an SQL query. The value of the element stored in the dialogue is therefore not saved in the database.
Alternatively, the property ReadOnly can be used.
Process & subtable fields
Process- & subtable fields can be used if the name of the process/subtable fields are enclosed in round brackets.
SELECT * FROM TABELLE1 WHERE GEBUCHT = (gebucht)
Special attention is needed when using these fields in combination with native SQL functions. To ensure that these functions are correctly called, the query must be set in brackets as follows:
SELECT * FROM TABELLE1 WHERE GEBUCHT = LOWER((gebucht))
If you want to access the actual column of the database table within the round brackets, a table alias has to be used. So name conflicts can be eliminated.
SELECT * FROM TABELLE1 AS t WHERE GEBUCHT = LOWER(t.gebucht)
Please note: If the variable resolutions should be prevented (e.g. when using the square brackets syntax with the name of a database column), a white space must be inserted between the opening and closing brackets and the "word" in between. This prevents the expression from being interpreted as a variable.
