Basic Teradata BTEQ Loop Help

[EC417]

Hello All, 

New Alteryx user with what is probably a very basic question. 

In a Teradata environment I will write a query for the data I am trying to extract. To avoid spool issues I keep the query limited to a month at a time.  The issue is I normally need about 3 years worth of data. 

Pre Alteryx I would write a BTEQ script and pass it through a python loop.  The loop would adjust the where clause of the BTEQ script, execute and repeat. 

How would you do this in Alteryx?  In my head... I want Alteryx to open a BTEQ.txt file, replace a given string, save.. .execute.. repeat. 

I was able to get pretty close with a batch macro and dynamic input but it seems like some of the BTEQ commands cause an issue. (".IF" for example)

Being a new user... I would like to hear how some of the more seasoned users would approach this instead of just forcing the way I used to do things into Alteryx.  

Thank you! 

[RishiK]

@EC417 the batch macro is the way in my opinion for this.

Instead of applying the BTEQ commands direct, why dont you connect in-db with the database environment and use the Alteryx workflow to apply the logic?

You may need to parse the BTEQ commands to get them working or can easily convert into the workflow.

[EC417]

Thank you @rishik at least it seems like I am headed in the right direction.  

Keeping things in BTEQ means they can be run in SQL Assistant which help in collaboration with users who do not have Alteryx. 

I was able to get it working by passing a text file, updating the relevant dates then executing.

but have 2 minor things I hope can be worked out... 

First...  how can I choose a non-delimited file? The screen shot below is the only way I could get it working but if somehow a tilde finds its way in the data it will mess me up. 

Second... I have the source script saved in the macro itself... how would I pass the source script INTO the macro so the macro can be kept generic? Has to be a real simple step I am missing.