ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Dynamically updated InDB SQL

tonyp
10 - Fireball

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?

tonyp
10 - Fireball

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.

 

tonyp_0-1596649100606.png

 

Labels