Minus

We can compare two queries by db engine (on one server) using MINUS/EXCEPT Sql operator

Following query is executed in db engine:

(Q1 EXCEPT Q2) Union ALL (Q2 EXCEPT Q1)

Do not put semicolon at the end of the query.

<cmpSqlResultsTest>
    <compare mode="MINUS" defaultDatasourceName="SQL_SERVER" diffTableSize="5" minusQueryIndicatorOn="true" fileOutputOn="false">
        <sql minusQueryIndicatorOccurence="1" minusQueryIndicatorText="ImportLayer">
         <![CDATA[
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        ]]>
        </sql>
        <sql minusQueryIndicatorOccurence="1" minusQueryIndicatorText="ModelLayer" >
        <![CDATA[
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        ]]>
        </sql>
    </compare>
</cmpSqlResultsTest>

• defaultDatasourceName - name of connection to database engine defined in cmpSqlResults-config.xml file
• diffTableSize - number of differences which will be displayed in logs. Even records from THE first DB/SQL and odd from the second (so the results table will have two times more rows than defined here). Default is 5.

When diffTableSize="0" is set - count query is executed:

Select count(*) from ((Q1 EXCEPT Q2) Union (Q2 EXCEPT Q1)) countTable

and minusQueryIndicatorOn="true" is ignored

• minusQueryIndicatorOn - by default it is set to 'false'; Minus Query Indicator for easier results analysis of Minus\Except query; if set to 'true' a text column is added, given occurrence set in minusQueryIndicatorOccurence (find by regex expression "(?i:From)") of 'From' clause in queries is replaced by given text in minusQueryIndicatorText
• fileOutputOn - boolean value - if true all results will be saved in CSV files (same name as XML test configuration file but with different prefix and extension). File may be incomplete (if chunk > 0 - see above).In case of MINUS comparator only 1 file will be generated: test_name_minus.csv - CSV with results returned by MINUS sql query Default is false.
• minusQueryIndicatorOccurence - by default it is set to 1, Nth occurrence of 'From' clause to be replaced when minusQueryIndicatorOn is set to 'true'; "*" (instead of an integer) replaces all 'From' occurrences in both the compared SQL queries
• minusQueryIndicatorText - by default it is set accordingly to 'query1' or 'query2' when minusQueryIndicatorOn is set to 'true', 'text' column added to queries for easiest analysis of found diffrences.

(         
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ImportLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        
         EXCEPT
        
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ImportLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        
        ) UNION ALL (
        
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ModelLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        
         EXCEPT
         
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ModelLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        )

The last column indicates queries in which differences were found

[minus_many_xml_files.PersonAddress_SQL_SERVER_first] Differences in SQL queries found -  expected [0] but found [2]
[minus_many_xml_files.PersonAddress_SQL_SERVER_first] The table of differences (2 rows):
 ______________________________________________________________________________________________________________________ 
|AddressID|AddressLine1        |AddressLine2|City     |StateProvinceID|PostalCode|ModifiedDate           |             |
|_________|____________________|____________|_________|_______________|__________|_______________________|_____________|
|'2'      |'9833 Mt. Dias Blv.'|'<NULL>'    |'Bothell'|'79'           |'98011'   |'2003-01-01 00:00:00.0'|'ImportLayer'|
|'1'      |'1970 Napa Ct.'     |'<NULL>'    |'Bothell'|'79'           |'98011'   |'2002-01-04 00:00:00.0'|'ModelLayer' |
|_________|____________________|____________|_________|_______________|__________|_______________________|_____________|

Please note that for Teradata "MINUS ALL" clause is used instead of just "MINUS".