Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Paramater Passing into SQL

SJBI
8 - Asteroid

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

18 REPLIES 18
PamW
Alteryx Alumni (Retired)

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:

 

simpledateworkflow.png

 

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

 

1-input.png

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:

1-textbox-to-enter-year.png

 

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:

3-errormessage-toforce-user-to-enter-4-digits.png

 

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. 

4-Action.png

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

pamW
SJBI
8 - Asteroid

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
Alteryx Alumni (Retired)

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)....

pamW
SJBI
8 - Asteroid

Mix report input issue.png

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).

strin.jpg

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
Alteryx Alumni (Retired)

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.) 

 

pamW
SJBI
8 - Asteroid

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

PamW
Alteryx Alumni (Retired)

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
pamW
SJBI
8 - Asteroid

I used PamW Thank you every one for your suport & help.

derekc
6 - Meteoroid

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

Labels