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
AndrewW
11 - Bolide

Check the Dynamic Input tool where you can Modify SQL Query, changing your SQL statement on the fly.

SJBI
8 - Asteroid

Can u explain little more.

I already have the query how to add extra where clause in this Dynamic input?

AndrewW
11 - Bolide

Sure, you have your text box that updates a value I assume, the value being 20160000 in this case. In the Dynamic Input tool you initially load your select statement to give the tool a template it expects the inputs to return (Input Data Source Template).

 

In your case that could be:

 

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

 

In the Dynamic Input choose Modify SQL Query, Add, Replace a Specific String. The 'Text to Replace' would be 20160000 and the Replacement Field would be the field where the value returned from the text box is written (which I assume writes into a field).

SJBI
8 - Asteroid

kkk1111.png

I used source First input source there I gave query in the input source. 

When I try to use dynamic input It says it must have input. Can you correct me if I'm wrong.

AndrewW
11 - Bolide

You don't need to have a standard Input tool feeding the dynamic input. Better to have a Text Input tool containg your Date variable, which you would connect up to your Text Box.

 

In the Dynamic Input tool, at the top, it says Input Data Source Template. That is the equivalent of the standard Input tool. Load your initial SQL statement there, just as you would with a standard Input tool.

 

Once that is done you can Modify SQL Query using the Date variable to Replace a Specific String.

SJBI
8 - Asteroid

abcd_test123.jpg

Sorry for the late reply.

I'm passing in this way. In dynamic input I selected "Modify SQL Query" and add "replace specifing string" but I dont see my year instead I see my whole SQL query. How to add this could you help me.

 

Query is like :

from a inner join b on a.x=b.x and b.mox<5 and a.kk="ABC" and B.year=20160000.

Here only the b.year i must pass wthough as parameter. 

AndrewW
11 - Bolide

Not sure if it's just me but this new forum style is very hard to work out what's going on...

 

Anyway, you're more or less there. Where it says 'replace specific string' you should only leave the bit of your SQL statement you want to replace there. In your case it would be 20160000. Delete the rest of the SQL statement from that box. Whatever is in the box is the string that will be replaced.

SJBI
8 - Asteroid

Hi Andrew,

Thank you for your quick turn around. I got one issue When I run using app I got and error when I checked the log Isee the number which I'm passing is comig in and but I missed with column name some thing like this I got

Ex:

where a.x=b.y and a.a1="A" and b,mn=12 and 20160000

v1.jpg

AndrewW
11 - Bolide

Check the string you're replacing and what you're replacing it with - there appears to be an issue somewhere with the replacement. It's tough for me to troubleshoot without the workflow.

Labels