Adding Filter Queries
Filter queries provide the user with the ability to add ad-hoc step filtering criteria to a template in order to improve the applicability of a job to the customer and where possible remove unnecessary manual steps.
This section discusses how to:
Create Filter Query Step type.
Assign Filter Query to a step.
Cut and Paste filter queries
Create and execute jobs that include filter queries.
Creating Filter Query Step Type
A template can have 0–n Filter Query steps defined and they can be defined at any position in the template.
Image: Filter Query Step dialog box
This example illustrates the fields and controls on the Filter Query Step dialog box. You can find definitions for the fields and controls later on this page.
The Script/Procedure is defaulted to Filter<n+1> where n is set to 0 for a new template, and incremented by 1 each time a Filter Query step type is added and saved.
The first Filter Query step created in a template will be named Filter1, the second will be named Filter2, and so on.
These values will always be unique, deletion of a Filter Query, will not change the n value. When a Query Filter is deleted, the value is never reused.
Note: The Step name cannot be used to determine uniqueness as Change Assistant allows multiple steps of the same name to exist in a template as long as they do not exist in the same task.
- Define Filter Query
Use this button to access the Define Filter Queries dialog box.
The following step attributes can be set for Filter Query step type:
From Tools release
Type of Upgrade
The following step attributes cannot be changed and will be greyed out.
Parameters — not required for Query Filter step type
Run Location (Default = Local)
Allow for Errors (Default = No)
Run Concurrently (Default = No)
The Filter Queries tab is not available, as:
A filter query step cannot be filtered by it’s own query.
A filter query step can not be filtered by a filter query defined in another step.
Defining Filter Queries
The Define Filter Queries page is used to define the filter query. The grid is displayed in read-only mode and always contains a minimum of one row in the view.
To add a new filter query, right-click and select Add New Filter Query.
To delete an existing filter query, right-click on the row and select Delete Selected Filter Query.
To update an existing filter query either:
Right-click on the row and select Edit Selected Filter Query.
Double click on the row.
Use the Filter Definition dialog box to define the filter.
Image: Filter Definition dialog box
This example illustrates the fields and controls on the Filter Definition dialog box. You can find definitions for the fields and controls later on this page.
- Filter ID
The Filter ID must be unique within the Filter Query step. The Filter ID value in conjunction with the Script/Procedure name is used to ensure uniqueness within the template. For example: Filter1–AP Check.
Enter a description for the filter query.
- Query Definition
Enter the SQL statement for the query.
Special filter queries are also available as described in the next section.
Note: The SQL statement is not validated.
- Filter if True Value
The Filter if True value can be a numeric or a string. Valid operator are:
=, <, >, >=, <=, <>
Contains, Does Not Contain
When the Filter if True Value is True then the associated step will be filtered.
Special Filter Queries
In addition to the standard user defined queries, a special platform-based filter query is available where Change Assistant leverages it’s own metadata about the environment to resolve the query.
The following would be inserted in lieu of the actual SQL statement. The variables cannot be used as a part of the actual SQL statement. Examples of unique identifier pairing would be:
The return value for these pairings will be Y for yes and N for no.
For more information on variables see Filter Query Variables
Assigning Filter Query to a Step
To use a filter query, the filter query must be assigned to a step. The following rules apply:
The drop down list of filter queries will be restricted to filter queries that are defined in the template at a position ahead or above the step being updated.
Steps can only be associated to Filter Queries that would execute prior to the step being filtered.
Steps cannot be associated to the same Filter Query twice.
Adding a Query Filter to a Step
To add a query filter to a step:
Double-click on the step to open the Step properties.
Select the Filter Queries tab.
Image: Filter Queries dialog box
This example illustrates the fields and controls on the Filter Queries dialog box. You can find definitions for the fields and controls later on this page.
- Step will be filtered if:
Select when filtering will be done.
All Filter Queries listed Return True Values
One Filter Query listed returns True value
- Filter Name
Select the Filter name from the drop down list.
To add additional filter queries, right-click and select Insert Query.
- Filter Identifier
Select the Filter Identifier from the drop down list.
Note: The drop down list will contain all of the Filter IDs for the selected Filter Name. This field will be grayed out if a Filter name has not been specified.
To add additional Filter Queries, right-click on a row and select Insert Query.
Deleting a Query Filter from a Step
To delete a query filter:
Double-click on the step to open the Step properties.
Select the Filter Queries tab.
Right-click on the row with the filter and select Delete Query.
Cutting and Pasting Filter Queries
Once Filter Query Step types have been defined and used as filters for additional steps, you must take care in deleting or moving the steps in the template. Keep in mind the following points:
Deleting a Filter Query Step
If you delete a Filter Query step from the template, you will receive a warning that the deletion will result in all references to all filters defined in that step will be deleted. If you choose to continue, the process will remove all step references to the deleted Script/Procedure + Filter ID values.
Cutting and pasting a Filter Query Step to another location in the template
Pasting to a position above or ahead of the original position has no issues.
Pasting to a position below the original position will result in a warning message indicating that moving step will result in removal of references to the current filter query step above it’s new position.
If you choose to continue, the process will remove all step references to the moved Script/Procedure + Filter ID values in steps that precede it in the revised template.
Cutting and pasting a non-filter query step
Pasting to a position below the original position has no issues.
Pasting to a position above the original position will result in a warning message indicating that moving the step will result in removal of references to filter queries below it’s new position.
If you choose to continue, the process will remove all step references to the impacted Script/Procedure + Filter ID values within the relocated step.
Creating and Executing Jobs that Include Filter Queries
This section discusses how filter queries affect:
At the time of job creation, Filter Query steps can be filtered by any of the standard attributes (such as Platforms) as is the case for any other Step type. The filtering of a Filter Query step in this manner does not invalidate or cause steps that reference the filter query to filtered.
At job creation all Filter Queries defined within the template will be set to a default value of Null or No Value. No Step will be filtered by an Ad-Hoc Filter Query at job creation, because no Filter queries have been run at that time. The earliest these Filter Queries can be executed is in the first step of the template/job.
A step can be filtered by a System Filter Query at job creation, assuming that the associated step does not also have any ad-hoc Filter Queries associated to it.
On Filter Query step execution, results are retrieved for all filter query ID’ s defined within that specific Filter Query step. These results are written to the Change Assistant database.
The execution accounts for the orientation of the Filter Query step.
The return values for each Filter Query are noted in the CA log listing the Filter Identifier, Filter ID, the Hide if True Condition, the return value and whether the Hide if True Condition was met.
In the event of a SQL error, an error message is written to the CA log and the step is marked as Failed.
In the event that the SQL returns a value of the wrong type (numeric as string), an error message is written to the CA log and the step is marked as Failed.
Upon completion of this execution, the template is refreshed and as part of this action filtering of steps will occur. Filtering of steps by filter query can only occur where all associated Filter Identifier’ s have been resolved and are not in the Null or no value state. This is true even when only a single FALSE is required to filter a step.
When a Job is opened or refreshed – the filter query filtering will execute to ensure that all required filtering has occurred.