Extract rules — The Filter Criteria tab

The Filter Criteria tab lets you extract values that have changed since the last time the Extract rule was run, or that satisfy any filter conditions you specify involving properties of the rules's primary class.

Use Last Updated Time as Start

For classes that include the property pxCommitDateTime, select this check box to extract all data that was created or updated since the last time the Extract rule was run, up to five minutes prior to the start of the extract. The five minute gap is included to avoid potential issues and discrepancies when running from the command line. This field is not relevant until the Extract rule has run at least once.

Note:

If you select the incremental extraction option, a filter condition is automatically included during an extract that restricts extraction to class instances where the pxCommitDateTime property value is greater than or equal to the last date and time when the extract ran.

pxCommitDateTime is automatically set for each class instance when it is changed in the database by the Pega Platform database regardless of how the data was created or changed (interactively by an end user, programmatically by an activity, through an import, or by any other means). This condition is added to any others you specify on this tab.

Sometimes the pxCommitDateTime property may need to be added as a database column to the class table. For example, it is not added automatically on upgrades. An extract using the incremental extraction property will fail if this column does not exist in the class table. Also note that when incremental extraction is performed, class instances with NULL values of the pxCommitDateTime property will be skipped and when using the -c command line option to extract from child classes, those child classes whose class tables do not include the pxCommitDateTime property will be skipped.

Do not select this check box if you plan to run the Extract rule using the command-line and intend to use the -d, -D, -u, and -U parameters. This is because these parameters do not override the check box setting , which can result in unanticipated results from a command-line extract.

Skip standard filters

Specifies whether to add the default filter condition on pzInskey /pxObjClass columns in the where clause of the source query during the extraction process.

  • When selected, the source query will not have the default filter condition on pzInskey /pxObjClass columns (even when the -c command line option is enabled) in the where clause during the extraction process.
  • When cleared, the source query will have the default filter condition on pzInskey /pxObjClass columns in the where clause during the extraction process. This is the default.

Skip standard order by clause

Specifies whether to add default sorting on the pzInsKey column to the source query during the extraction process.

  • When selected, then order by clause based on pzInskey column will not be added to the source query during the extraction process.
  • When cleared, then order by clause based on pzInskey column will be added to the source query during the extraction process. This is the default.

When cleared and the -n command-line option is used, the order by clause based on the pzInsKey column is not added to the source query during the extraction process to provide backward compatibility. Prior to the introduction of this field, if the -n command-line option was specified, the order by clause based on pzInsKey column was not added to the source query during the extraction process.

Logic

Based on the Label values in the Criteria array, enter a logical expression that defines how the system combines the criteria into an overall Boolean value at runtime. The expression can include parentheses and the operators AND and OR.

For example, if the table contains four rows labeled A, B, C, and D, you can enter logic such as:

(A OR B OR C) AND D

Notation for this field is similar to the logic statement in When condition rules.

If you create a logic statement, it must include all labeled rows. If you do not include a logic statement, the system selects data instances for which all logic rows return the value True.

Note: You must include at least one logical expression in the Criteria array.

Label

Optional. An entry in this field is used in the Logic field. The entry must:

  • Be unique
  • Start with a letter
  • Have only alphanumeric characters
  • Have no spaces

If you enter multiple criteria, enter a letter or letters to uniquely identify the row. For instance, if you have three rows, their labels can be A, B, and C.

Field

Enter a property reference to provide the filter values. You can only use properties exposed as individual columns. Start the property reference with a period.

Click Edit to the right of the field to create a new property.

You can specify single value properties that are produced by an SQL function. To use the result of an SQL function query, click the Calculation Builder icon to the right of the field. In the Calculation Builder, select an SQL function and specify the parameters.

Note:

Do not use properties of type TextEncrypted.

Data page references, unexposed property references, linked property references and non- scalar property references such as embedded properties, page lists, page groups, value lists, and value groups are not supported.

Operator

Select a comparison condition, such as Is Equal or Starts With.

At runtime, BIX converts the operator comparison condition to a standard SQL comparison statement.

Note: Non-standard operators, such as the Oracle CONTAINS feature, are not available.

Value

Enter or select one of the following comparison values:

  • A literal constant, formatted in accordance with the property type. See Constants in expressions Developer help topic Constants in expressions for formatting help. Place a backslash character in front of any double-quote character that displays within the constant (\").

  • A fully qualified property reference to a single value property that is present on the clipboard at run time. The type of this property must match the type of the property in the Column field.

  • A reference to a parameter that is defined in the report's rule, in the format param.name.

  • The name of another single value property that is exposed as a column. This allows you to compare the values of two properties in the same database row if their types are identical or allow comparison.

  • A calculation. To compare it with the result of a calculation, click the Calculation Builder icon to the right of the field. In the Calculation Builder, select an SQL function and specify the parameters.

  • A symbolic date, such as Yesterday or Current Year for a Date or DateTime value. When the list value rule executes, this symbolic reference is converted to an actual date or date range based on the time zone of the user and the condition value. For example, if you select Last Year and the condition value is IS EQUAL TO, the result is a date range in the previous calendar year between January 1 and December 31 inclusive.

  • For a DateTime property, you can specify the symbolic value Last Extraction Time. At runtime, this value is replaced with the date and time when the extract rule last ran. You can define a filter using this value as an alternative to checking the Use Last Updated Time as Start option described above, to perform incremental extraction based on any DateTime property you choose.

If the Condition field is set to IS NULL or IS NOT NULL, leave the Value field blank.

Note: Unexposed property references, linked property references and non- scalar property references such as embedded properties, page lists, page groups, value lists, and value groups are not supported.

Use null if empty

This option is important only when there is no value in the Value field at run time. By default, when the Value field is empty at run time, the criteria defined by this row are ignored, and processing is the same as if this row is not present.

For example, assume the criteria are that the customer's last name starts with a given letter, and the value set in the Value field is Param.Letter. If this extract runs at a time when Param.Letter has the value "C", the report will only contain instances in which the Lastname property value starts with the letter C. However, if the same extract rule runs again when the Param.Letter parameter has no value, this criteria is dropped, so the report will contain instances where the Lastname property value has any value or no value.

When the default behavior is not desirable, select the Use null if empty check box to force the condition value to become IS NULL when the Value value is blank at run time. In the above example, the criteria is transformed to "Lastname is null" which is different than having no restriction on Lastname.

Ignore case

Select this option if you want run time comparisons of the Field value and the Value value to be case-insensitive. For example, "Smith" matches "SMITH" and also matches "sMiTH". If selected, at run time "a" matches "A" and is less than "B".

When you do not select this option, comparison of the Field value and the Value value occurs without case conversion. In this case, "a" is greater than both "A" and "B".

CAUTION:
In most cases, do not select this option. Select this box only when necessary to obtain the rows of the report:
  • If your source database for the extract is hosted by Microsoft SQL Server, comparisons are always case-insensitive. Selecting this option does not affect the report contents, but might increase processing time.
  • If your source database for the extract is hosted by Oracle, IBM Db2 or most database vendor products, selecting this option might affect which rows appear in the report when values occur in mixed case. Conversion to uppercase can significantly slow down database processing.
  • Case conversion is meaningful only for properties of type Text, Identifier, or Password. Regardless of database software, case conversion is not needed for numbers, dates, or text that contains only uppercase or lowercase characters.