Submit multiple SQL lines to Teradata through R

zyxhrb · ‎05-11-2016

Hello Everyone,

I am having a problem of running queries through R. Here is the error message "Only an ET or null statement is legal after a DDL Statement". I've done my researches: I know one way to fix is to run the queries in separate calls. However, given I have so many existing queries, I AM WONDERING IF THERE IS A WAY I COULD DO IT IN THE SINGLE CALL (basically I just want to open my existing sql files and copy/paste):

library(RODBC)

channel <- odbcDriverConnect("dsn=Enterprise Data Warehouse;uid=123123;pwd=123123")
sqlQuery(channel,
         "
         create volatile table fast_list as ( 
         select cust_nbr
         from customer
         where natl_acct_nbr ='1234' 
         ) with data  primary index (cust_nbr) on commit preserve rows ;

         insert into fast_list (cust_nbr) values (1235);
         ")

I have seen posts:

1. suggest add "ET;" and I tried to add between "on commit preserve rows;" and "intert...", it didn't work.

2. suggest add "session mode = Teradata". I tried to add this into odbcDriversConnect and it didn't work either.

Thanks in advance!