Deleting records using teradataR


Deleting records using teradataR

Hi everyone. 

I have created a loop that will do the following 

foreach( date ){
-empty tables (2)
-fill first table with values based on pre-existing data
-fill second table with values based on pre-existing data and first table
-return second table to R
-store column sums for second table

My problem is that I am not able to run steps 1 & 2 since they do not return resultSets and tdQuery requires this. Is there a different function or a way to circumvent this?

Before anybody advises that I rework my SQL code, please note that steps 2 and 3 are in their ultimate forms, that which will run regularly on our servers; this loop will only run once on past data. Additionally, I am already near my limits on how much computational power/memory I can use. Also, there are deadlines involved so it is preferable that I not have to learn a new language (BTEQ). What are my options? 

Thank you all in advance. 

Tags (1)
Senior Supporter

Re: Deleting records using teradataR

you can use also direct RJDBC to exec queries in TD

drv = JDBC("com.teradata.jdbc.TeraDriver","/PATH/TeraJDBC__indep_indep.","/PATH/TeraJDBC__indep_indep.")

conn = dbConnect(drv,"jdbc:teradata://System/ TMODE=ANSI,CHARSET=UTF8",user="XXX",password="YYY",dbname="ZZZ")

t <- dbSendUpdate(conn,"create table ZZZ.calendar as (select * from sys_calendar.calendar) with data unique primary index (calendar_date)")
t <- dbSendUpdate(conn,"drop table ZZZ.calendar ")

This works from my mac to TD.

Re: Deleting records using teradataR

I wouldn't have jikely dug into the RJDBC syntax if someone simply pointed me in that direction, but the code you provided got me going very quickly. Thank you very much.