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

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.

SimpleDateSQL.yxzp