ODBC Driver changing submitted SQL

Database

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 1

This 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 = Yes
When this option is Yes, the ODBC Driver for
Teradata prepares SQL statements enclosed in
parenthesis or preceded by comments.
When this option is No, these statements are
prepared by Teradata

NoScan

= [ Yes | No ] Default = No
This option is used to enable/disable parsing
of 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 driver
is bypassed and the SQL statement is sent
directly to Teradata.
This option should not be set when the SQL
statement contains ODBC-specific syntax.
Setting this option while using ODBC-specific
syntax in the SQL statement results in
Teradata RDBMS reporting errors.
When NoScan is No - the p****r in the driver
is not bypassed and SQL statements are sent to
the 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?
5 REPLIES

Re: ODBC Driver changing submitted SQL

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

Re: ODBC Driver changing submitted SQL

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.

Re: ODBC Driver changing submitted SQL

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.

Re: ODBC Driver changing submitted SQL

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.

Re: ODBC Driver changing submitted SQL

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.