SQL expression attribute properties

The following interface is displayed when configuring a SQL expression attribute:

A SQL expression attribute allows you to expose a valid SQL expression as an attribute.

A SQL expression attribute has the following specific properties:

Target Table: you must browse for a Target table using the Choose Database Item dialog. When you do so, a list of database tables is displayed in the Choose Item section. Select the required database table and click OK.

If one or more auxiliary databases have been configured at the current RPI tenant, you can use the Choose Database selector to select the database from which the list of tables is retrieved. If auxiliary databases are available, the table is displayed at the property as ‘[Database name].[Schema].[Table name]’.

For more information on using the Choose Database Item dialog, please see the RPI Framework documentation.

Expression: a valid SQL statement, provision of which is mandatory. Expression has no practicable length limit. Note that, when building a SQL expression attribute, you can use the string ‘{alias}’ to represent an alias to the attribute's Target table.
Data Type: a dropdown, exposing values BigInt, Date, DateTime, Decimal, Integer and String (the default). A validation error will be raised if the selected value is incompatible with the Expression provided. Note that Data type is used when creating temporary tables; if you wish to be prescriptive about the data type of the values the attribute is to expose, you will need to configure manually within the SQL expression using your own casting/converting logic.
Data Length: this property is only enabled for the String data type. It is an integer and must be greater than 0 if supplied. Its default value is 50. A warning is displayed at validation results if the length provided results in truncation of sample data. However, if you wish, you may proceed with attribute creation, and no run-time truncation of attribute values will take place. Note that Data length is used when creating temporary tables; if you wish to be prescriptive about the data length of the values the attribute is to expose, you will need to configure manually within the SQL expression using your own casting/converting logic.
Validate: a button with text set to 'Test to ensure the SQL for this attribute is valid'. Clicking the button creates a Validate SQL Expression job, which checks that the expression entered:
- Is valid
- Does not contain one or more reserved strings. By default, reserved strings are as follows:
  - ;
  - insert
  - delete
  - drop
  - -
  - truncate
  - update
  - %3B
  - union
  - create

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.

Results: one of:
- SQL is valid
- SQL is invalid. Change the expression and re-test.

(Any error details are also displayed).

Example: if the SQL expression is valid, an example of the results of its execution is displayed.

On invocation of Finish, a warning message is displayed if the SQL expression has not been validated.

When used in a selection rule or another context of execution, the attribute's expression is evaluated inline in a SQL statement. Note that you will need to ensure that you fully qualify any columns contained therein if the expression contains any cross-table references.

Note that the ability to create a new or configure an existing SQL expression attribute is controlled by the 'Attribute SQL Expression' functional permission.