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

Tools & Utilities
Enthusiast

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

I am running a query that is giving two different result set for particular two columns in Teradata SQL assistant (Teradata mode) versus ANSI mode (in datastage tool).

I am not sure what is causing to completely change value the values of two columns when running in Teradata mode versus ANSI mode. Completely different values is coming for the same two column when mode changes from TERADATA to ANSI.

 

Can someone help me out to find the exact reason for this mismatch?

 

Thanks in advance.

 

Regards,

Mayank

 

 

Regards,
Mayank
22 REPLIES
Senior Apprentice

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

Hi Mayank,

 

Can we see some SQL?, data?, table definitions?

 

The most common reason for differences is case sensitivity. By default Teradata mode is not case sensitive whereas ANSI mode is case sensitive. So a WHERE clause such as:

WHERE last_name = 'wellman'

In Teradata mode this will find 'wellman', 'Wellman', 'WELLMAN' etc.

In ANSI mode this will find 'wellman' but not 'Wellman' or 'WELLMAN'

 

This case sensitivity is not just restricted to '=' conditions, it can also affect range tests against character data. So a WHERE clause such as:

WHERE last_name BETWEEN 'r' AND 't'

In Teradata mode this will find 'Ruskyn','smith' and 'Thompson'.

In ANSI mode this will find 'smith' but not the others.

 

This issue can be resolved by using (NOT CS) in your WHERE clause, as:

WHERE last_name = 'wellman' (NOT CS)

 

A less common cause is if you're using temporary tables and have defaulted to 'ON COMMIT DELETE ROWS'.

In Teradata mode if you populate a temp table and then read it, by default the read will find no data because the populate and read are in separate transactions.

In ANSi mode the same processing would find the data because by default they are in the same transaction.

 

This issue can be resolved a number of ways, my preferred is to use 'ON COMMIT PRESERVE ROWS' in the temp table definition.

 

HTH

Dave

 

 

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

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

The above is only looking at common differences in session mode.

 

Have you also checked the following between SQLA and Data Stage:

- same default database

- same user name (but only if you're using row level security - which in my experience is unlikely)

- same session temporal qualifier (if using this feature)

 

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,

Case sensitivity is not the issue as I am just selecting the column from a table with some joins. FYI, the SQL for you below.
The issue is that only the values of top two columns in select statement are getting changed when executed in different modes (Teradata vs ANSI). The different value that is coming for top two column in ANSI mode is actually the value of some other column within the same table.

SELECT
RELETHN.BLEND_TYP_DSC
,RELETHN.BLEND_IND
,RELETHN.BLEND_TYP_DSC_IND
,MBC.CD_SR
,RELETHN.SRC_CD
FROM
(
SELECT
PYMB.PYMBI_KEY,
PYMB.IND_NBR,
MB.BE_DT,
MB.ED_DT,
MB.MR_NBR,
MB.JOIN_ID,
MB.CD_SR,
RANK() OVER
(PARTITION BY PYMB.PYMBI_KEY, PYMB.IND_NBR
ORDER BY MB.BE_DT DESC, MB.ED_DT DESC) XRANK
QUALIFY XRANK = 1

FROM mthdata.cntrt MB

INNER JOIN mthdata.mbdata PYMB
ON MB.MB_KEY = PYMB.MB_KEY

WHERE MB.CD_SR = 'MC'
AND MB.BE_DT <= '2017-07-31'

) MBLIST
INNER JOIN mthdata.IND PYMBI
ON MBLIST.PYMBI_KEY = PYMBI.CURR_KEY
INNER JOIN mthdata.cntrt MBC
ON MBLIST.JOIN_ID = MBC.JOIN_ID
INNER JOIN mthdata.OFFER CU
ON MBC.OFFER_ID = CU.OFFER_ID
LEFT OUTER JOIN opt.best RELETHN
ON PYMBI.KEY_JOIN = RELETHN.KEY_JOIN
AND RELETHN.SRC_CD = 'MC'
LEFT OUTER JOIN mthdata.probl relrace
ON PYMBI.MB_KEY = RELRACE.KEY_JOIN
ORDER BY MBC.MR_NBR

FYI, the value MC used in where clause is coming in all caps in the table and I have also tried WHERE MB.CD_SR = 'MC' (NOT CS)

Regards,
Mayank

Regards,
Mayank
Senior Apprentice

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

Hi Mayank,

 

Thanks for the SQL.

 

Although I only referred to selection criteria and case-sensitivity but this can also affect joins.

 

Looking at your sql there are a number of places where case sensitivity could be the issue. For example, in your derived table you've got this join: "MB.MB_KEY = PYMB.MB_KEY"

- if those columns are character data then the results of the join may be affected.

 

You've tried the actual selection criteria and that hasn't made any difference. Can I suggest you try using the (NOT CS) option on all joins and selection involving character data types.

 

Looking at your SQL:

- I'm assuming that "MB.BE_DT" (in the derived table) is a date column, so ignore that.

- In the re-written SQL shown below I've assumed that all join and selection columns (except MB.BE_DT) are character columns and so I've added "(NOT CS)" 'everywhere'. You need to remove them from any non-character columns.

SELECT RELETHN.BLEND_TYP_DSC
      ,RELETHN.BLEND_IND
      ,RELETHN.BLEND_TYP_DSC_IND
      ,MBC.CD_SR
      ,RELETHN.SRC_CD
FROM (SELECT PYMB.PYMBI_KEY,
             PYMB.IND_NBR,
             MB.BE_DT,
             MB.ED_DT,
             MB.MR_NBR,
             MB.JOIN_ID,
             MB.CD_SR,
             RANK() OVER(PARTITION BY PYMB.PYMBI_KEY, PYMB.IND_NBR ORDER BY MB.BE_DT DESC, MB.ED_DT DESC) XRANK
             QUALIFY XRANK = 1
      FROM mthdata.cntrt MB
      INNER JOIN mthdata.mbdata PYMB
         ON MB.MB_KEY (NOT CS) = PYMB.MB_KEY (NOT CS)
       WHERE MB.CD_SR = 'MC'
         AND MB.BE_DT <= '2017-07-31'
      ) MBLIST
INNER JOIN mthdata.IND PYMBI
  ON MBLIST.PYMBI_KEY  (NOT CS) = PYMBI.CURR_KEY (NOT CS)
INNER JOIN mthdata.cntrt MBC
  ON MBLIST.JOIN_ID  (NOT CS) = MBC.JOIN_ID (NOT CS)
INNER JOIN mthdata.OFFER CU
  ON MBC.OFFER_ID  (NOT CS)= CU.OFFER_ID (NOT CS)
LEFT OUTER JOIN opt.best RELETHN
  ON PYMBI.KEY_JOIN  (NOT CS) = RELETHN.KEY_JOIN (NOT CS)
   AND RELETHN.SRC_CD = 'MC' (NOT CS)
LEFT OUTER JOIN mthdata.probl relrace
  ON PYMBI.MB_KEY  (NOT CS) = RELRACE.KEY_JOIN (NOT CS)
ORDER BY MBC.MR_NBR;

The last thing to consider here might be the 'ORDER BY' clause. If that is a character column and your difference is (for example) 'a' vs. 'A' then this might be a consideration. To be honest I'm not sure about that, I've certainly never seen it as an issue.

 

A couple of other things to consider/try:

1) Another thing to try may be to run the query only in SQLA, once using Teradata mode and once using ANSI mode. If you get the same answers there then this is a DataStage issue and not an sql/dbms issue.

2) On the join columns do you have matching data types? Again I can't be certain that this will cause a difference between session modes, but it might be worth a look.

 

Cheers,

Dave

 

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

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

A further thing to try would be to just run the derived table SQL by itself in both session modes. Do you get the same results from each run? If you do then that bit of the processing is eliminated as the cause, if not then fix that first and see what else happens.

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,

Thanks for multiple solutions. I have given the answers of your questions highlighted in RED below, please take a look.

 

Looking at your SQL:

- I'm assuming that "MB.BE_DT" (in the derived table) is a date column, so ignore that. -- Yes, the columns with suffix _DT are date columns

- In the re-written SQL shown below I've assumed that all join and selection columns (except MB.BE_DT) are character columns and so I've added "(NOT CS)" 'everywhere'. You need to remove them from any non-character columns. -- All the column used in join with suffix either _KEY or _ID  are INTEGER or DECIMAL columns. (None of join condition columns are character columns).

 

The last thing to consider here might be the 'ORDER BY' clause. If that is a character column and your difference is (for example) 'a' vs. 'A' then this might be a consideration. To be honest I'm not sure about that, I've certainly never seen it as an issue. -- Yes the order by column is VARCHAR column but I tried using NOT CS on it but the issue still persists

 

A couple of other things to consider/try:

1) Another thing to try may be to run the query only in SQLA, once using Teradata mode and once using ANSI mode. If you get the same answers there then this is a DataStage issue and not an sql/dbms issue. --  How to switch mode in SQLA, can you please assist how to do it, I tried but it didn't work.

2) On the join columns do you have matching data types? Again I can't be certain that this will cause a difference between session modes, but it might be worth a look. -- Yes all the join columns have matching data types.

 

The only option left is 1 which you suggested to change modes in SQLA and try and see if i get same answers, please assist me in that and let me know if something else you think may create am issue.

 

Regards,

Mayank

 

Regards,
Mayank
Senior Apprentice

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

Hi Mayank,

 

Changing mode in SQLA depends on whether you're using ODBC or Teradata.NET.

 

Teradata.Net:

  1. Click the 'connect' icon to display the 'Connection Information' dialog.
  2. Switch to the 'Advanced' tab.
  3. Set the 'Session Mode' to Teradata or ANSI as required.
  4. FIll in logon details on 'Basic' tab and logon.

 

ODBC

  1. From the menu bar, choose 'Tools / Define ODBC Data Source'
  2. Double-click the DSN that you want to use (to show the 'ODBC Driver Setup for Teradata Database' dialog).
  3. Click the 'Options' button (bottom right of dialog).
  4. Set the 'Session Mode' to Teradata or ANSI as required.
  5. Save changes.
  6. Click the 'connect' icon to display the 'Select Data Source' dialog.
  7. Double-click the DSN that you want to use (to show the 'Teradata Database Connect' dialog).
  8. FIll in logon details and logon. 

 

If using ODBC and your laptop is locked down such that you cannot change 'System DSN' information, try creating a 'User DSN' instead.

 

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 use Teradata.net to login and I switched to ANSI and then TERADATA mode and ran the query in both the modes and I am getting same issue which I got through Datastage.

 

The values of top two columns in SELECT statement is getting changed when I run the query in ANSI mode and the changed value that I am getting in ANSI mode are actually the values of some other column within the same table.

 

Let me know your thoughts.

 

Regards,

Mayank

Regards,
Mayank
Senior Apprentice

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

Hi Mayank,

 

Ok, so it definitely looks like this is an ANSI vs. Teradata 'thing'.

 

Can you please provide an example of the two outputs? At least  a few rows of data.

 

You say "The values of top two columns in SELECT statement is getting changed when I run the query in ANSI mode and the changed value that I am getting in ANSI mode are actually the values of some other column within the same table."

- just so that I know we're talking about the same thing, you are referring to columns RELETHN.BLEND_TYP_DSC and
RELETHN.BLEND_IND.

 

When you say "are actually the values of some other column within the same table"

- are you talking about values of other columns in the same row or values of the same column but in a different row?

 

Again, an example of your output - both in ANSI and Teradata mode would be useful.

 

Perhaps more useful in terms of resolving this, are you able to provide a small sample of input data? Although looking at your query it involves 7 tables so that may not be an easy thing to do.

 

Now that you have SQLA setup to switch between ANSI and Teradata mode have you tried running the derived table portion of your query by itself? If so does that show any difference.

  (On another point, did you know that with SQLA you can have multiple connections open to the same system at the same time? So you could have one connection open in ANSI mode and another open in Teradata mode. This will make it much easier to try the two modes. If you cannot logon to second data source, go into "Tools / Options", select the 'General' category and tick the top option "Allow connections to multiple...". if you do this, when you logon the second time you will get a second query window. Just remember which one is ANSI and which one is Teradata!).

 

Cheers,

Dave

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