Iterative Macro to Replace String Value in SQL of Connect In-DB Input Tools

[Swest4190]

Good Morning Community, 

I have an automated workflow that takes snapshot of various pieces of sales data from Teradata for the current date, merges them all together, then writes them to Tableau Server and Teradata user DB table. The end result is a table that gives a snapshot of every customer account (current amounts outstanding, amounts past due, ect) for that day. Each day, this workflow generates roughly 50,000 records. 

I recently had to make a slight tweak to the queries inside each of the Connect In-DB tools and now need a way to back fill the rest of the data (roughly 4 months worth). My initial thought was to create an iterative macro that would read a text table containing all the dates i need to run the query for, and replace where i reference CURRENT_DATE in the Connect In-DB queires with those dates on each iteration.  The trouble is, i cannot seem to find the correct way to configure the tools to do so. I tried to convert all of the Connect In-DB tools to dynamic inputs and go that route, but since i am using some ranking functions, and utilizing TD_DAY_OF_WEEK in these queries, the workflow would error out. 

Each of the In-DB connections circled below is a query that, among other things, filters the data by utilizing CURRENT_DATE and i essentially need to replace the CURRENT_DATE with one of the dates i need to back fill my table on each iteration. It is also worth noting that some of these SQL queries are fairly complex and utilize unions and call CURRENT_DATE multiple times in each query (luckily ever instance CURRENT_DATE is used is an instance that needs to be replaced with an iterative date) in order to capture transaction categories, ect. 

1) Is this even possible?

2) If so, any tips or wisdom on how to go about configuration?

Thanks,

Steve

[JoeS · Accepted solution]

Hi @Swest4190 

I think you need to create a batch macro rather than an iterative macro.

If you create a control parameter that contains CURRENT_DATE, then using an action on each Connect In-DB replace the word "CURRENT_DATE" this will replace all instances in your query.

Then in a workflow outside of the batch macro pass in data as the CURRENT_DATE, with each row representing each date you need the instance to run. 

As a note, it will run them in the order the data is presenting, so if you do need to run them chronologically, you can make sure the data is sorted that way before going into the control parameter.

[Swest4190]

wow i feel dumb...

 

So i converted it to a batch macro and confirmed that it runs correctly by itself. Screenshot below

The issue is that i am getting several errors when i implement this macro into a workflow to read in the dates i need:

Error: Historical Run (5): Record #1: Tool #101: Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Function 'TD_DAY_OF_WEEK' called with an invalid number or type of parameters
Error: Historical Run (5): Record #1: Tool #145: Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Datatype Mismatch in THEN/ELSE expression.
Error: Historical Run (5): Record #1: Tool #153: Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Datatype Mismatch in THEN/ELSE expression.
Error: Historical Run (5): Record #1: Tool #160: Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Function 'TD_DAY_OF_WEEK' called with an invalid number or type of parameters
Info: Historical Run (5): 1 iterations were run (ended by a downstream tool)
Error: Historical Run (5): The output connection "Output" was not valid

[danilang · Accepted solution]

Hi @Swest4190 

If the macro runs correctly on its own but doesn't in a workflow, check these 

1. Is the control parameter in the main workflow the correct type and format.  I notice that your date field is actually a string.

2. Do the Action tools in the Macro do what you think they do.  If you turn on Show all Macro Messages in the Workflow Configuration Runtime tab, you should be able to see the actual Sql that is executed.  Another option here is the run the macro in debug mode from the Interface designer screen. 

For the 2 final messages

Info: Historical Run (5): 1 iterations were run (ended by a downstream tool)
Error: Historical Run (5): The output connection "Output" was not valid

These are caused by the fact the output of the macro is not connected to anything.  Connect a browse tool to the output and it will complete.

Dan