community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

SQL Statement With Multiple Parameters

Asteroid

Alteryx Community, happy Thursday!!

 

I have a SQL Query running against a DB2 database.  The query has a where statement with five optional parameters.

Query Input2.PNG

I have successfully connected to the database in Alteryx, and passed one parameter to the query, receiving the expected record set.  To do so, my workflow looks like this:

 

Query Input3.PNG

I cannot connect more than one text input tool to my dynamic input tool, so I cannot figure out how to pass each parameter to my query.

 

How do I pass five optional, prompted parameters?

 

 

 

 

 

Highlighted
Comet

In your Dynamic Input tool, you can add multiple conditions of "Replace a specific string"

 

sql.PNG

Then you just have to connect multiple Text Box / Update Action tools to the Text Input tool which has one column for each parameter.

 

If you want the parameters to be optional, then you'll have to do more manipulation of the SQL code. 

Pulsar

You can connect multiple text boxes and action tools to your Text Input tool. 

 

Put your where clause with placeholders in a cell in your text input tool like this, and use a text box with action tool to update each of the placeholders.

 

WHERE CUPUCA.UCPREF = ARPPH.PHPREF AND ARPPH.PHCUNO = ARLCU.CUCUNO AND (((UCMSG = PLACEHOLDER1 OR UCMSG = PLACEHOLDER2 AND UCMSG <> PLACEHOLDER3 AND UCADJC = PLACEHOLDER4)) AND CUNAME = PLACEHOLDER5)

 

placeholders.png

 

If some of them are optional, i.e. they could be missing, you'll have to do it slightly differently by building the where clause in a formula tool with if statements, something like this:

 

where clause.png

 

both sample workflows attached

 

 

Meteor

If the replace a specific string doesn't work in your dynamic input tool, my workaround for most things similar to this is to just add a second table with the text input tool with all of your query parameters, then append the data to your initial query and run it through a formula tool with the formula [Query]+" "+[Clauses] and then pass that formula field through as your query in the dynamic input tool. Using the text input tool keeps you from having to add multiple formulas or tools to your workflow, just structure the input as such: 

 

Clauses
Clause 1
Clause 2
Clause 3

 

then when you append the data to your initial query and pass through the formula tool it does the same work as adding a bunch of text inputs, conditional formulas, or action tools.

 

Greenshot 2018-11-29 20.07.53.png

Asteroid

David, I am working through your second example file.  I am confused (as usual lol).  After the formula tool, how do I connect to a dynamic input tool to query my db2 database.  Will it not work this way?

Comet

If you're going with the route of building up the WHERE clause in the Formula tool as @DavidP suggested, then in your Dynamic Input tool you'll have to use replace specific string on the entire WHERE clause.

 

where1.PNG

Also, if you're using this route then there's no need to write out all the parameters in your template SQL in the Dynamic Input tool. You can specify something short in the WHERE clause (e.g. "... WHERE 1=1") since it will all be replaced anyways.

Pulsar

This is how I meant for you to configure your dynamic input tool.

 

Click Edit under Input Data Source Template and set up a connection to your database with a SQL query the way you would in a normal Input Data Tool.

 

Then select Read a List of Data Sources, pick WHERE CLAUSE as your field and select the option Change File/Table Name.

 

The dynamic input tool will then replace your sample SQL query as set up in the template with the query in the WHERE CLAUSE field.

 

dynamic input select statement.png

Asteroid

Thanks for responding (again) David.  So here is where we're at.  I'm getting two errors, one of which I have consistently had all along and cannot figure out how to fix. (The conversion error)

 

N parameter6.PNG

Pulsar

I would do the following to troubleshoot the 1st error.

 

In a new workflow, connect to your database with an Input Data tool and write the entire SQL statement in the SQL editor and get it to work. Then copy the entire sql query into the formula tool as a new field and use that field as the field in your dynamic input tool.

 

If this doesn't work, something is wrong in the dynamic input tool configuration and if it does work, something is wrong with the sql query you construct in [WHERE CLAUSE]. Compare [WHERE CLAUSE] with the SQL query you wrote and see if there is a difference. It looks like the problem is with the syntax of your SQL query.

 

I'm not sure about 2nd error.

Labels