Skip to main content

Get help for ARIS Process Mining functions and services

Define filter using SQL statements

You can use SQL statements to filter the extracted table data.

To create SQL statements, you need SQL programming skills. For details on using the appropriate SQL statements, please refer to the documentation of your source system database.

Note

When using dynamic parameters or other parameters of the Date data type, such as "Task start time", you can convert these parameters into any format using the format SQL function. For details on how to use the format function, see the Supported SQL functions chapter.

If the SQL statement is not correct, an error is displayed during data extraction.

Example 228.

You can use the following SQL statement to filter the extracted processes.

DOCTYPE = 'SALES DOCUMENT' AND YEAR = '${DOCUMENT_YEAR}'


Procedure. Procedure

  1. Open the Extractions section of a data set.

  2. Click a JDBC extraction in the Extractions list. The Tables page of the selected extraction opens.

  3. Select a table and click ... Show details > Edit.

  4. Enter an SQL statement in the Filter statement box to define a filter.

  5. Insert a column name of the column you want to extract. You can insert column names in the statement using the list of previously selected columns.

    1. Place the mouse pointer on a position in the SQL statement.

    2. Click Insert columns and select the columns.

    3. Click the column that you want to insert into the statement.

      The name of the selected column is placed at the cursor position in the statement.

  6. Insert parameters. You can insert parameters using the parameter list that provides the global and local parameters you have created.

    1. Place the mouse pointer on a position in the SQL statement.

    2. Click Insert parameter. The Add parameter dialog opens.

    3. Click the parameter that you want to insert into the statement.

      The identifier of the selected parameter is placed at the cursor position in the statement. The dialog closes automatically.

  7. Click Done.

You have specified an SQL statement to filter the table extraction.

Tip

You can use the automatic code completion for defining a filter statement in the following ways. The code completion supports the format function.

  • Place the mouse pointer on a position in the SQL statement and press the Strg and 'Space' keys. A list with the format function, extraction and data set parameters, and configured columns of the source table is displayed. Select an element from the list. The element is inserted into the statement.

  • Place the mouse pointer on a position in the SQL statement, press 'f', select 'format', and press 'Enter'. The format function snippet is inserted and a parameter can be selected. You can now either select a parameter from the list or enter the parameter manually.

  • Insert a parameter into the SQL statement. Move the mouse pointer between '{' and the first letter of the parameter placeholder. Press 'f', followed by Ctrl + space. The format function is suggested. Press 'Enter' to insert the format function.