All Forums Database
williamdieter 15 posts Joined 03/06
09 Jun 2006
ODBC Driver changing submitted SQL

Hey everyone, we're experiencing some strange behavior between an application and Teradata. Essentially, we are submitting a query, but the query is being rewritten. We have run some checks to see if the query is being rewritten by the application, or by Teradata.This is what we are submitting:sel a.column1, cast( case when a.column2 < 5000 then u.column3 when a.column2 >= 5000 then s.column4 end as decimal(7,5) ) as column5 from container1.table1 a inner join container2.table2 t on t.column1 = a.column1 LeFt OuteR JoIn container1.table3 s on s.orig_acct_num = a.column1 LeFt OuteR JoIn container1.table4 u on u.orig_acct_num = a.column1 order by 1This is what is stored in the DBC query log:SELECT a.column1 , CAST( column2 AS DEC ( 7 , 5 ) ) AS column5 FROM container1.table1 a INNER JOIN container2.table2 t ON t.column1 = a.column1 LEFT OUTER JOIN container1.table3t s ON s.orig_acct_num = a.column1 LEFT OUTER JOIN container1.table4 u ON u.orig_acct_num = a.column1 ORDER BY 1 I've also used a Teradata specific function (add_months) and the case was changed to ADD_MONTHS, so we know the query is being rewritten by something that knows Teradata functions. I have looked through the ODBC documentation, and have found two options that might affect this.SQLWithCommentsOrParenthesis= [Yes | No]Default = YesWhen this option is Yes, the ODBC Driver forTeradata prepares SQL statements enclosed inparenthesis or preceded by comments.When this option is No, these statements areprepared by TeradataNoScan = [ Yes | No ] Default = NoThis option is used to enable/disable parsingof SQL statements by the driver.In Windows, this option is called"DisableParsing".When NoScan is Yes - the p a r s e r in the driveris bypassed and the SQL statement is sentdirectly to Teradata.This option should not be set when the SQLstatement contains ODBC-specific syntax.Setting this option while using ODBC-specificsyntax in the SQL statement results inTeradata RDBMS reporting errors.When NoScan is No - the p****r in the driveris not bypassed and SQL statements are sent tothe p a r s e r.Another thing that is stumping me, is the fact that I can run the query through SQL Assistant, and not encounter the same problem. I can't find any documentation on how the ODBC P a r s e r works.Has anyone experienced anything like this before? Does anyone know where I can find more documentation on the ODBC P a r s e r for Teradata?

DGiabbai 47 posts Joined 07/04
11 Jun 2006

Before going further on investigating, you'd try to enclose the case expression between parenthesis:cast( (case....end) as ...)Also, try executing the query via CLI (using BTEQ, for example). And last but not least, concentrate on results: if results are not what you were expecting, then could it be a (serious) bug? Report to TD-CS...

williamdieter 15 posts Joined 03/06
12 Jun 2006

We got it to work, that's no biggie. I'm just trying to figure out why we were getting these results in case there was a bug. It looks like it's not Teradata that's doing it. If I execute the query through SQL Assistant, it runs fine. I found out late friday that the application uses Data Direct's ODBC driver, which leads me to believe the issue is in DD's ODBC p****r.

williamdieter 15 posts Joined 03/06
12 Jun 2006

We got it to work, that's no biggie. I'm just trying to figure out why we were getting these results in case there was a bug. It looks like it's not Teradata that's doing it. If I execute the query through SQL Assistant, it runs fine. I found out late friday that the application uses Data Direct's ODBC driver, which leads me to believe the issue is in DD's ODBC p****r.

williamdieter 15 posts Joined 03/06
12 Jun 2006

We got it to work, that's no biggie. I'm just trying to figure out why we were getting these results in case there was a bug. It looks like it's not Teradata that's doing it. If I execute the query through SQL Assistant, it runs fine. I found out late friday that the application uses Data Direct's ODBC driver, which leads me to believe the issue is in DD's ODBC p a r s e r.

williamdieter 15 posts Joined 03/06
12 Jun 2006

We got it to work, that's no biggie. I'm just trying to figure out why we were getting these results in case there was a bug. It looks like it's not Teradata that's doing it. If I execute the query through SQL Assistant, it runs fine. I found out late friday that the application uses Data Direct's ODBC driver, which leads me to believe the issue is in DD's ODBC p a r s e r.

You must sign in to leave a comment.