Dynamically updated InDB SQL

[tonypreece]

I have a list of customer IDs in a CSV that I need to query against a Teradata database.

Ideally I'd create a temporary table with the IDs and run a JOIN in my SQL, but the company will only allow read only access to TD so I can't create a temporary table. That means I can build the query with Connect InDB, but I can't use the Data Stream In tool.

Can anyone think of an alternative way to achieve this in Designer?

[tonypreece · Accepted solution]

I found the answer after posing the question and was going to delete this, but instead I'll share the answer for others to find.

The key is the Dynamic Input In-DB tool which is hidden by default so I hadn't noticed it existed before now.

First was to get the list of IDs into one field, I used @Joe_Mako's excellent concatenation macro to achieve this.

With all my customer IDs in one comma separated list in a single field, I moved onto building the SQL query into a another field.

The query looks like this: Select x,y,z from tableA where CIN IN ([This Range]).

To build it I used a Text Input tool with three columns:

[Connection Name]: This is the connection name you have set up to connect to your database (in my case, the ODBC connection name).

[QueryPartA] - contained the SQL query text upto and including the bracket: Select x,y,z from tableA where CIN IN (

[QueryPartB] - contained the final closing bracket

I used an Append Fields tool to connect the text input to the concatenated customer list and then joined QueryPartA with the customer list and QueryPartB in the Formula tool

[QueryString] = [QueryPartA]+[CINList]+[QueryPartB]

Then comes the Dynamic Input InDB tool. There are two variables to select, the Connection Name Field and Query Field. Just pick the field names from your data and your'e ready to go!

I hope this might help someone else at some point.