I have two tables having around 172 columns each with same data loaded (80 records) from one table(Staging) to another (target).
For the Testing purpose I need to validate the data load using minus query between both the tables.
Here, I am facing a strange issue in which when I am doing minus for entire 172 columns from each of tables the SQL Result with all 80 records returned.
But on the contrary when I break the minus SQL in two parts (say midway) with half the no. of columns and run the SQL, the resultset is Zero records for both SQLs (executed seperatly which as per expected result
SEL (172 Columns) From Target
SEL (172 Columns) From Stage;
Expected Result>> No records should be returned
Actual Result>> All 80 records are returned
SEL (first 100 Columns) From Target
SEL (first 100 Columns) From Stage;
SEL (remaining 72 Columns) From Target
SEL (remaining 72 Columns) From Stage;
Result for both>> 0 records returned
Can anyone please suggest on this unique behaviour of Teradata for minus query operation and what is actually inside Teradata during the minus query process. ??
Is there any limitataion minus set operation Teradata database..?
Verify the column ordering is the same for both tables in the first query. There should be no difference in behavior based on number of columns. If you can't find anything wrong with the query, open an incident with Teradata Support.
Duplicate rows will be eliminated from the first subquery - only one copy will be kept - since this is MINUS instead of MINUS ALL. Then it will do a "minus join", typically after redistributing both tables based on the hash of all the columns, matching that rowhash first and then the entire row. Matching rows are discarded and unmatched rows from the first table are returned.