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

[AndrewW]

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

[SJBI]

Can u explain little more.

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

[AndrewW]

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]

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]

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]

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]

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]

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

[AndrewW]

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.