Matching Equivalent SQL Strings

Sometimes reports in the base project and the target project include SQL that is functionally equivalent but slightly different. For example, reports in the base project might use a table prefix of TEST while reports in the target project use a table prefix of PROD. You want Integrity Manager to treat the table prefixes as identical for purposes of comparison, because reports that differ only in their table prefixes should be considered identical.

In this case, you can use the SQL Replacement feature to replace TEST with PREFIX in the base project, and PROD with PREFIX in the target project. Now, when Integrity Manager compares the report SQL, it treats all occurrences of TEST in the base and PROD in the target as PREFIX, so they are not considered to be differences.

The changes made in the SQL Replacement Table are not stored in the <ID>_<GUID>.sql files for each report. Rather, Integrity Manager stores those changes in memory when it executes the integrity test.

To Treat a Specific SQL String as Matching

1. Create a comparative integrity test.
2. On the Select Processing Options page, select the SQL check box.
3. Click Advanced Options, select the SQL/MDX category.
4. In the From column, enter the SQL that appears in the report.
5. In the To column, enter the replacement text.
6. From the Apply To drop-down list, select whether the replacement should apply to the base project, only the target project, or both projects.
7. SQL replacements are made in the order they appear in the table, from top to bottom. Click Up or Down to move selected entries in the table.
8. Click OK. When the test is executed, the SQL strings specified in the From column are treated as though they are the strings in the To column.

Related Topics

Advanced Options: SQL/MDX

Select Processing Options Page

Ignoring Dynamic SQL when Comparing SQL

Tests that can be Performed in Integrity Manager