Update Multiple Parts of SQL Statement in Dynamic Input Tool

[cjaneczko]

I am currently using a list to update the WHERE clause in my SQL statement to pull only a certain list of materials. But I would also like to have a prompt for the number of months of data to pull. Is there a way to have an additional part of the SQL statement updated?

This is an example of the SQL statement. Right now the Dynamic Input Tool is updating the ('Update', 'This') using the list of Materials connected to this tool. I would also like to update the "12" in the posting date to have that be input by the user of the workflow. So if they wanted only 6 months of data, they could input a 6 to override the 12. 

select  db.table_Field1 as "Material",
	db.table_Field2 as "Customer_No",
	db.table_Field3 as QTY,
	db.table_Field4 as "Post_Date",
from db.table 
where db.table_Field1 in ('Update', 'This') 
	and db.table_Field4 >= Add_Months(Trunc(SysDate, 'month'), -12) 
	and db.table_Field4 < Trunc(SysDate, 'month') 

[danilang]

Hi @cjaneczko 

Add a "Replace a Specific String" parameter in your Dynamic Input config and have it replace the "12" in your SQL string

Dan

[cjaneczko]

@danilang So I tried that but its asking for a Replacement Field. The only option in the dropdown is the field that contains the list of Materials connect to the input tool.

[danilang]

Hi @cjaneczko 

You need to supply the value that will replace the 12 as field in your input.  

Dan

[cjaneczko]

I ended up having to generate the WHERE clause with a formula by concatenating multiple fields into one field. Couldnt figure out how to supply the Dynamic Input tool with multiple fields. It would only accept one.

[apathetichell]

just replace the entire query using dynamic input in-db.