Teradata Row limitation & behaviour with minus SQL query

Database

Teradata Row limitation & behaviour with minus SQL query

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

SQL:-

SEL (172 Columns) From Target

Minus 

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

Minus 

SEL (first 100 Columns) From Stage;

SEL (remaining 72 Columns) From Target

Minus 

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..?

3 REPLIES
Teradata Employee

Re: Teradata Row limitation & behaviour with minus SQL query

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.

Enthusiast

Re: Teradata Row limitation & behaviour with minus SQL query

Check for nulls..

Enthusiast

Re: Teradata Row limitation & behaviour with minus SQL query

Seems, first 100 columns returns expected result. This gives right direction that, data stored in column 101..172 have the problem. As Yuva stated check for NULL's. And another important area to check is "padding space" to Character columns. Sometimes Decimal values with different (scale) range result in difference.

--concatenate all CHAR & VARCHAR columns from 101..175 from tables-1 & table-2 and check the difference

SELECT

CAST(column101 as VARCHAR(99)) ||

CAST(column102 as VARCHAR(99)) ||

CAST(column103 as VARCHAR(99)) ||

CAST(column104 as VARCHAR(99)) ||

.....

FROM <table-1>

UNION ALL

SELECT

CAST(column101 as VARCHAR(99)) ||

CAST(column102 as VARCHAR(99)) ||

CAST(column103 as VARCHAR(99)) ||

CAST(column104 as VARCHAR(99)) ||

.....

FROM <table-2>

ORDER BY 1

;

--Add Decimal columns if you could not trace any difference from above query.

Thanks!!