Update Multiple Parts of SQL Statement in Dynamic Input Tool

Question

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

apathetichell · Answer

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

cjaneczko · Answer

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.

danilang · Answer

Hi @cjaneczko

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

Dan