About updating sql query

[vaishalilambe17]

Hi there,

I am trying to update where caluse in my nested SQL query using dynamic input tool

It looks like 

Where Date = To_Date('10-01-2019','MM-DD-YYYY')

I tried doing String replace but its not working

Also tried where caluse update but its not getting pre popultaed in dropdown to update in dynamic tool.

I am 

Please guide how can I do it. I need help asap.

[MichaelSu]

Hi @vaishalilambe17,

Sounds like the issue might be within the dynamic input tool. Be sure to include the Database location under the input data source template / edit tab, and then under Modify SQL Query the 'Where Clause' should populate.

Thanks,

Mike

[vaishalilambe17]

its not pre populating where clause as I am using to_Date function in my where clause.

[MichaelSu]

Can you please provide a screenshot of your dynamic input config?

Note that you could always use the interface tools as well to interact with your Where Clause

[vaishalilambe17]

here you go. I am using string replACE AT THE MOMENT BUT ITS NOT WORKING.

aS MY WHERE CALUSE IS NOT GETTING PRE POPULATED IN THE UPDATE WHERE CALUSE WINDOW.

[vaishalilambe17]

This is how my sql query looks -

Select * From (Select * From <table_name>where <column_date> <= To_Date('10-01-2019', 'MM-DD-YYYY') And <column_date2> <= To_Date('10-01-2019', 'MM-DD-YYYY') And <column_date3>= To_Date('10-01-2019', 'MM-DD-YYYY') <table names>

his query runs as expected when I run it seperately and pulls data, but when with dynamic input I am trying to update where cluase for date prior ro today's date, its not working.

[MichaelSu]

It's tough to say what is causing the Where Clause not to populate without having the workflow itself. A potential work around is to create a batch macro to update the where Clause with a list of dates that you would like to leverage. Note that this might take a bit longer and utilize more resources.

For Example, in the below I am updating the Customer ID in the Where Clause with the list of ID's I feed it from the Input tool.

[vaishalilambe17]

i know the reason. if I remove to_Date and keep only '10-01-2019' it prepoulates, but i need to_date

[danilang]

Hi @vaishalilambe17 

Instead of using "Update Where Clause", which replaces your entire where clause with the value in the field, try using "Replace A Specific String"  

This should update all 3 of your date values with the value in StartDate, without removing the "To_Date("  

Another alternative is the use a Formula tool to build your entire where clause including all the "To_date(" conversion functions and using this as your replacement field

Dan