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.
A template can have 0–n Filter Query steps defined and they can be defined at any position in the template. The location of the filter query step will determine when the filter queries associated to that step will be executed.
This example illustrates the fields and controls on the Step Properties for Type FilterQuery. You can find definitions for the fields and controls later on this page.
Field or Control |
Description |
|---|---|
Script/Procedure |
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 Filter0001, the second will be named Filter0002, and so on. These values must be unique, deletion of a filter query step, will not change the n value. When a filter query step is deleted, the value is never reused within the current template. |
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
Orientation
Products
Platforms
Languages
Apply Type
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)
Note: 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.
The Define Filter Queries page is used to define the actual filter queries. 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.
Defining Filter
Use the Filter Definition dialog box to define the filter.
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.
Field or Control |
Description |
|---|---|
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. |
Description |
Enter a description for the filter query. |
System Query |
Select this radio button to use a system queries. System queries include:
|
User Defined |
Select this radio button to type your own query definition. |
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 must be a select statement, however the select statement is not validated. |
Filter if True Value |
The Filter if True Value can be a numeric or a string. Valid operator are:
For a single filter query, when the Filter if True Value is True then the associated step will be filtered. |
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 executed.
Steps cannot be associated to the same Filter Query twice.
Adding a Filter Query to a Step
To add a filter query to a step:
Double-click on the step to open the Step properties.
Select the Filter Queries tab.
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.
Field or Control |
Description |
|---|---|
Step will be filtered if: |
Select when filtering will be done.
|
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 Filter Query from a Step
To delete a filter query:
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.
Click OK.
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 impact.
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 any exist.
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 impact.
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 any exist.
If you choose to continue, the process will remove all step references to the impacted Script/Procedure + Filter ID values within the relocated step.
This section discusses how filter queries affect:
Job Creation
Job Execution
Job Creation
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.
Job Execution
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. All the filter query results are also written to a log file. Each filter step generates a log file with results named to match the filter value for example filter0001_out.log.
When a Job is opened or refreshed – the filter query filtering will execute to ensure that all required filtering has occurred. All steps filtered by Filter query are listed in the job.log file.