0

The VB for application version is 7.1; Teradata version 16.20.53.27.
The SQL statement is very long, the length of it is 59972.I run it into Teradata directly, it works well. I use the following to execute this SQL in VBA:

set connection = CreateOjbect("ADODB.connection")
connection.open "DSN=xxx UID=XXX PWD=XXX"
Set ObjRecordSet=CreateObject("ADO.RecordSet")
ObjRecordSet.open StrQuery connection
ObjRecordSet.close
connection.close

the error message is Run-time error '-2147217833 (80040e57) [Teradata] [ODBC Teradata Driver]Teradata Database
A character string failed conversion to a numeric value

I check the long SQL statement, could not find the convert function for numeric value, only converting string into varchar(80). Thanks!

fred wu
  • 55
  • 6
  • There might be an *automatic* conversion from string to numeric, e.g. comparing a VarChar column to a number (In Explain you will see `(FLOAT FORMAT '-9.99999999999999E-999')`). But this should happen when you directly run it, too. – dnoeth Sep 18 '21 at 10:16

1 Answers1

0

After separating the long SQL query, I find the root cause:

select * 
from table1 
where column1 = '29991231'

while data type of column1 is date. It can be executed in Teradata SQL assist while it fails in VBA.

I change it to be:

select * 
from table1 
where column1 = to_date('31-12-2999', 'DD-MM-YYYY')

it has no error when VBA call this SQL query.

Anyway, thanks for your attention!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fred wu
  • 55
  • 6
  • Btw, the recommended way to write a date is a Standard SQL Date Literal: `DATE '2999-12-31'`, shorter than to_date :-) – dnoeth Sep 19 '21 at 10:17