modify in-database query

[jeffv]

Hi all,

Sometimes I think I have things figured out with Alteryx and then something comes along that puts me back at the beginning of learning the product.

Using the dynamic input tool I've been able to change dynamically change a where clause.  However, the next step (since it takes forever to get some data from a certain sqlserver table) is to us in-database tools.  As an example, say I have a table t1 with two field f1 & f2.  The query I want to issue is  select * from t1 where f1 = my_input

Using the dynamic input in-db  I have two configurable items: Connection name field and query / query alias list field.  Pressing the help button really doesn't give me insight how to use the tool.  It doesn't seem like I can connect a Connect In-DB tool to it to give it a query.  Can I do a replacement of "my_Input"  in an in-Database tool?

I'm using 2018.3 designer.

Thanks,

Jeff

[MichalM]

@jeffv 

When you look at the Dynamic Input In-DB tool, it has a green anchor to the left and blue anchor to the right which means that you can feed it data from your local computer and afterwards it changes the context to In-DB. The Connection field represents your In-DB alias - connection name you'd use when configuring the Connect In-DB tool - and the query will be the query you'd like to run.

You could also rebuild the functionality using a batch macro - something like the below. The two control parameters - Connection & Query - will allow you to pass these values dynamically into the Connect InDB tool and replace the relevant part of the configuration. You can then add more steps into the process and stream the data out. 

Example workflow attached

[jeffv]

Thanks for response...  so let me clarify.  For the in database dynamic input on the green side you give it some input.  Should the fields be Connection and Query?  I tried both but then when I go into config of the in db dynamic input it say no valid fields so I must me doing something wrong.  Thoughts?  Is this a 32/64 bit issue?  I'm using 64 bit for sqlserver and for designer. (thought I read that somewhere).  Does the other method have a similar issue?

Thanks,

Jeff

[MichalM]

How you call the fields is not relevant - it's the details contained within them.

One of those fields needs to contain an In-DB alias - you can configure these database connections within Connect In-DB or under Options > Advanced Options > Manage In-DB Connections. The name of the connection is what the filed needs to contain.

64-bit is a prerequisite for the In-DB tools to work so you should be good.

Can you attach your workflow for me to have a look?

[jeffv]

MichalM,

Thanks for your help...

I'm taking baby steps....

Ok I have the db connection being passed in and the query executes.  But now How do I make the dynamic substitution...  ie as an example the query is: "select * from _t1 where f1 = 2".   Say I need to dynamically substitute the 2 for something else?

Thoughts?

Jeff

[MichalM · Accepted solution]

No problem @jeffv 

Depending on where the table and field information comes from, you can built the select statement in the Formula tool

"Select * from " + [T1] + " Where F2 = '" + [F2] + "'"

 

An alternative could be a batch macro approach similar to what I suggested for the Dynamic In-DB - you will have a SQL template which will be dynamically updated using values inserted via the control parameters.

Does that make sense?