Paramater Passing into SQL

[SJBI]

Hi All,

How to do Parameterization in the SQL. I created the Alteryx APP in my query I need to pass Year as 20160000. in  where clause. In Source the query is :

{Code}

from a inner join b where a.x=.b.x and b.year=20160000

I Created text box interface and I connected to the source but I couldn't figure out how to filter the year from source query.

Could any one help in resolving this issue..

Regards,

SJ

[PamW · Accepted solution]

You typically dynamically load from a database when you have a list of years that you were sending via a list to your dynamic input SQL.

Based on the thread what I would suggest is a slightly different approach. Instead of using a dynamic input directly modify the sql in an Input tool as follows. Note that my example uses SQL Lite and is attached for you to review.

The workflow:

1) Configure the input with your SQL statement that is functioning, in my example it is as follows:

The years i want to replace are highlighted red.

2. Set up a text input for the date (you could also use a numeric input if you want:

3) BEST PRACTICE not required but recommended... set up an error message to force the user to enter a date, you could be more forceful if you wanted and have it be between a particular years etc:

4. Set your action as "Update Value", select your SQL statement in the tree, Check the box to replace a specific string and delete all the text in that replace string text box, enter the year that appears in your SQL statement that you want to replace. 

Note that this can lead to problems if i had "2016' elsewhere in my SQL statement and I did not wish to replace it.

[SJBI]

Odbc Connection strings should be in form of Odbc:Connectionstring|Table.

This is the error I'm getting.

In my table My records are like YYYYMMDD

[PamW]

If you go to the Interface Designer (View > Interface Designer) there is an option to debug your app that is very helpful.

It sounds like your selection is wrong can you put a screen shot of your action? 

So you will notice that my dates were YYYY-MM-DD but I am only replacing the year and happen to be replacing it twice.

You should be selecting the line with your connection and SQL query and only have the value you want to replace in the text box below (do not replace any of the quotes because they are not really quotes they are ticks)....

[SJBI]

In my Sql where clause we give condition line Year>20160000.

Basically the year column in stored as number not as dates.

One more questioin I want to pass value as user pameter through interface(Text Box).

In the above method which you posted its like static. I used as "update value with formula" in the action  and trying to pass.plz correct me if I'm worng

[PamW]

If you select the SQL statement and use formula then you will have put in the entire SQL statement with the formula, in my example that would look like this:

// Connection string my example is a sqllite file
".\orders-with-dates.sqlite|" +

//SQL Statement with #1 from Text Box

"SELECT * FROM orders where orders.date >= '" +
[#1] + "-04-01' and orders.date <= '" +
[#1] +"-04-31'"

AlternativelY i could also do it this way:

replace([Destination],"2016",[#1])

However that is the exact same behavior as use select value and just changing the specific string I selected in the below example

How I know what is being replaced is when I open the View > Interface Designer and click on the Magic Wand option in the Interfacer Designer and enter in some text then click OPEN DEBUG the workflow below the log shows what was replaced in the tool and the Debug workflow should be able to run. (If it doesnt run then my action is not working correctly.) 

[SJBI]

Can you suggest me a blog in understaidng how interface works.

[PamW]

No but what I can recommend is the following virtual training video:

Go to the following page: http://www.alteryx.com/virtual-training#virtualprev

And find the following class: Analytic App Development for Intermediate Users

Analytic App Development for Intermediate Users

You've created some great workflows that others need to run. Learn how to turn your workflow into an analytic app by constructing a graphical user interface (GUI) so users can easily set the required parameters.

You'll learn how to:

• Choose from the range of interface tools to construct the best GUI
• Publish your app to the Alteryx Gallery for consumption by others

Tool sets/processes covered:

• Interface Tools

[SJBI]

I used PamW process which helped to solve the issue. Thank you every one for your suport & help.

[derekc]

Thanks for this very clear step-by-step explanation.  It worked on my first try. : )