Custom SQL Expressions

A custom SQL expression can be added to a standard selection rule using the Add Custom SQL Expression dialog.

It contains the following:

Query table: you must select the table against which the expression is to be executed, using the Choose Database Item dialog.

If the current RPI tenant is configured with one or more auxiliary databases, the dialog lists only those tables within the database matching the current standard selection rule’s resolution level.

• Query after WHERE clause: this multiline field allows you to define the SQL expression, provision of which is mandatory. The expression should take the form of a SQL WHERE clause (e.g. ‘Gender = ‘F’’). By default, the following reserved strings are not permitted within the expression:

  • ;

  • insert

  • delete

  • drop

  • –

  • truncate

  • update

  • %3B

  • union

  • create

  • exec

The default set of reserved strings can be overridden by setting application setting Override Custom SQL Reserved Words to True, and by specifying a list of reserved strings using application setting Custom SQL Reserved Words. If this is supplied, the existing reserved strings no longer apply.

You can use the following syntax within the Query to reference the Query table:

‘{alias}.’

Validate: this button allows you to test to ensure that the SQL you entered is valid. It is enabled when characters are present in Query after WHERE clause. When you click the button, it is initially disabled as it creates a Validate SQL Expression job (the My Jobs Dialog is not shown). If the SQL is valid, the property appears as follows:

If the SQL not is not valid, the following are shown:

You can amend the SQL and click the button again to re-validate. Note that the SQL you enter need not be valid for you to click Finish.

On clicking Finish, the custom SQL expression is added to the standard selection rule at the context of invocation. It is displayed as ‘Custom SQL Expression’.

If the selected Query table is not the same as the standard selection rule’s resolution table, the expression is added within a criteria list.

Having added a custom SQL expression, you can subsequently configure it to change its details.

Note that the ability to add a custom SQL expression is controlled by functional permission Selection Rule – Standard – Custom SQL Expression.