Subset by Expression
Subset based on expressions allow for more advanced selections of records. The expressions are defined in a SQL like syntax. You can choose multiple attributes from the repository list and create a SQL expression based on the attributes.
This type of a subset evaluates the list of records everytime it is used. As records are added or changed, the list of records selected by subset changes. The list of records shown for confirmation are the records which match the criterion at the time of definition and must be taken as an indicative list.
The expression is defined using the attributes display names and it is recommended that you use the list of attributes to avoid any spelling errors. This list includes system attributes as well.
For example, you may create a subset rule where all records have the string Paint in the description, a price range of $100-$150, and a UPC code containing the string 247. The expression for this would be as follows:
@Description like%’Paint’% AND @Price > 100 AND @Price < 150 AND @UPCCode like%’247’%.
The following table lists common SQL operations you can use for a subset expression rule.
| Operator | Description | Example |
|---|---|---|
| <Field> = <Field/Constant> | Equality | @UnitPrice=29 |
| <Field> |= <Field/Constant> | Inequality | @UnitPrice|=29 |
| <Field> > <Field/Constant> | Greater than | @UnitPrice>10 |
| <Field> >= <Field/Constant> | Greater than equal | @UnitPrice|>=29 |
| <Field> < <Field/Constant> | Less than | @UnitPrice<10 |
| <Field> <= <Field/Constant> | Less than equal | @UnitPrice|<29 |
| <Field> IN <Field/Constant> | Equal to any members in the list. | @UnitPrice IN(30,25,48) |
| <Field> [NOT] IN <Field/Constant> | Not equal to any members in the list. | @UnitPrice NOT IN(30,25,48) |
| <Field> [NOT] Between <Field/Constant> AND <Field/Constant> | Greater than equal to first value, and less than equal to second value. | @UnitPrice Between(10,100) |
| <Field> [NOT] Like%<Field/Constant>% | Match value in field. | Description Like%’Widget’% |
You need to specify different expressions for the Timestamp format as per database:
- For Oracle database: [TIMESTAMP_ATTRIBUTE_NAME]= to_timestamp('2012-11-21 10:12:34.003 AM','YYYY-MM-DD HH12:MI:SS.FF AM')
- For SQL Server database: [TIMESTAMP_ATTRIBUTE_NAME]= convert(datetime2,'2012-11-21 10:12:34.003 AM')
- For Postgres SQL database: [TIMESTAMP_ATTRIBUTE_NAME]= to_timestamp('2012-11-20 12:01:14.142 PM','YYYY-MM-DD HH24:MI:SS.MS PM')