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]%'
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.
