Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

About updating sql query

vaishalilambe17
8 - Asteroid

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.

8 REPLIES 8
MichaelSu
Alteryx Alumni (Retired)

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.

 

clipboard_image_1.png

 

clipboard_image_0.png

 

Thanks,

Mike

vaishalilambe17
8 - Asteroid

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

MichaelSu
Alteryx Alumni (Retired)

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
8 - Asteroid

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
8 - Asteroid

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
Alteryx Alumni (Retired)

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.

 

clipboard_image_0.png

 

clipboard_image_1.png

 

vaishalilambe17
8 - Asteroid

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

danilang
18 - Pollux
18 - Pollux

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"  

 

i.png

 

s.png

 

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 

Labels