Getting different results when executing SQL's in Teradata and ANSI mode

Tools & Utilities
Enthusiast

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Dave,

 

I have written the only the below query that is just selecting those records from one table (original table) only and I have attached a image comparing the two different result set I am getting when I am running same query in two different modes.

 

See the below image to see the issue and also the query

 

SELECT

RELETHN.BLEND_TYP_DSC
,RELETHN.ETHNC_TYP_DSC
,RELETHN.PRIMY_ETHNC_TYP_DSC
,RELETHN.BLEND_IND
,RELETHN.BLEND_IND2
,RELETHN.BLEND_TYP_DSC_IND

FROM opt.best RELETHN

 

 

 

Regards,
Mayank
Senior Apprentice

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Mayank,

 

That's a useful display.

 

Assuming that 'opt.best' is a table or a simple view (no joins, aggregations etc):-

- what you are seeing in those two answer sets are different rows, which is why you're seeing different values.

- if for instance you added an ORDER BY clause on a uniquely identifying column (or combination of columns) to this query then I'd expect to see consistent results.

 

But the above depends on 'opt.best' being a very simple structure.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Dave,

 

Opt.best is not a simple view. It is being bulit by joining with different tables, there is a long query written to build this view.

 

- what you are seeing in those two answer sets are different rows, which is why you're seeing different values. -- Can you please explain this, I m not very clear on this point?

Regards,
Mayank
Senior Apprentice

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Mayank,

 

I hope the following will explain my comment "- what you are seeing in those two answer sets are different rows, which is why you're seeing different values."

 

Let's start simple. Assume that you have the following data in a table:

 

Col1 Col2 Col3 Col4
1    a    Tom  Fred
2    A    Fred Tom

Assume that you now run the following query:

 

 

SELECT col3 AS C1, col4 AS C2
FROM table
WHERE col2 = 'A'
ORDER BY col1;

In ANSI mode the first row in your result set will show:

 

 

C1   C2
Fred Tom   <<< this is the row where 'col1 = 2'

In Teradata mode the first row will show:

 

C1  C2
Tom Fred <<< this is the row where 'col1 = 1'

The 'same query' is producing different results, but because the data is very simple we can see that the first row in each result set is actually a different row from the table.

 

I realise that your data and processing is much more complex, but the principle remains the same. Does this help?

 

Coming back to your test query from above (just running against 'opt.best':

- when run in ANSI mode and Teradata mode do you get the same number of rows in the result set? (this is the number of rows generated by the dbms before you hit any limit specified within SQLA)

- having run both, if you sort the answer set windows in SQLA do you now see the same data in the first row(s)? (this is only valid if you have returned the entire answer set to SQLA)

 

I notice that your images only show a few rows - which is understandable - but your query has no ORDER BY clause which typically means that the sequence of the returned rows is not guaranteed to be the same on multiple executions of the query.

 

If 'opt.best' is a view that "It is being built by joining with different tables" then any joins on character columns or selection criteria against character data in that view (and any nested views) also have to cater for differences between ANSI and Teradata mode and could cause differences.

 

Let's see how that takes us forward.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Dave,

 

I realise that your data and processing is much more complex, but the principle remains the same. Does this help? --Yes I am clear and thank you for the explananation

 

- when run in ANSI mode and Teradata mode do you get the same number of rows in the result set? (this is the number of rows generated by the dbms before you hit any limit specified within SQLA) -- Yes same number of rows in both result set (FYI, I had put a filte to restrict to return only 5 rows on the basis of KEY_JOIN (unique column/primary index) listed five join keys)

- having run both, if you sort the answer set windows in SQLA do you now see the same data in the first row(s)? (this is only valid if you have returned the entire answer set to SQLA) - Yes

 

Updated Query and answer set as below:

 

SELECT

RELETHN.KEY_JOIN
,RELETHN.BLEND_TYP_DSC
,RELETHN.ETHNC_TYP_DSC
,RELETHN.PRIMY_ETHNC_TYP_DSC
,RELETHN.BLEND_IND
,RELETHN.BLEND_IND2
,RELETHN.BLEND_TYP_DSC_IND

FROM opt.best RELETHN

WHERE RELETHN.KEY_JOIN IN

('8688882'
,'8688970'
,'8688971'
,'8688972'
,'8689025'
)

ORDER BY RELETHN.KEY_JOIN

 

 

 

 

Regards,
Mayank
Senior Apprentice

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi,

 

Using column KEY_JOIN is probably a good idea if it is unique within the relevant table.

I need to see the definition of the 'opt.best' view. Please show that.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi Dave,

 

The view definition is like as below:

 

Repalce view opt.best

AS 

Select 

<columns>

from

<different join (6-8 joins) with different tables>

 

One interesting fact that I noticed while seeing the query for this view is that the below two columns  are sourced from the same table.

 

,RELETHN.BLEND_TYP_DSC
,RELETHN.ETHNC_TYP_DSC

 

But I am wondering if these two are sourced from then will the result set change when session mode changes?

Regards,
Mayank
Senior Apprentice

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Hi,

 

The fact that multiple columns are sourced from the same table should not be an issue. Most queries (in my experience) use multiple columns from each table they reference).

 

Can you please post the full view definition. Without that it will be very hard for me to take this further.

 

If you don't want to post it on a public page then use the mail facility to send it to me directly.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Junior Contributor

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Looks like there are two joins to the same table within the opt.best view, at least one is an Outer Join plus a COALESCE to replace a missing 2nd ethnicity with the primary.

So check the join to the table with BLEND_IND & BLEND_TYP_DSC.

Senior Apprentice

Re: Getting different results when executing SQL's in Teradata and ANSI mode

Just had another thought.

 

As you're getting the same number of rows with both ANSI and Teradata and from your test query the same value for KEY_JOIN is being returned (so possibly the same rows), do you have a CASE expression included in the processing which changes the 'source' of values for final result columns? Remember that a CASE expression will include WHEN clauses which uses tests, which may produce different results based on sort order, case sensitivity etc. between ANSI and Teradata mode.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com