Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Update SQL in Input tool using action and text box

Highlighted
7 - Meteor

Hi everyone! 

 

I am trying to use the action tool to update the where clause in my query inside the input.

 

I have an input as :

SELECT * FROM MYTABLE WHERE CLIENT = 1

 

I use an action tool to update value where i replace the string 1.

So basically when i enter 2 in the text box the query changes to:

SELECT * FROM MYTABLE WHERE CLIENT = 2

 

what i want to achieve is to eliminate the where clause if the text box is left blank.

 

so for example, if nothing is entered inside the text box, i want the query to simply be:

SELECT * FROM MYTABLE

 

this will return data for all of my 10 clients. 

 

This is just a sample which is why I am specifically looking for a fix inside the query and not something that will update based on a filter tool.  

 

Any help will be appreciated.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @S-9 I mocked up a workflow let me know what you think?

Highlighted
7 - Meteor

This works well! Thank you!

 

Would like to add something to this.Suppose my query is now SELECT * FROM MYTABLE WHERE  DATE >= ('06-02-2020','MM-DD-YYYY')  AND CLIENT = 1 

 

I am using the same logic and joining all of this into the same action and using the formula.

IF IsEmpty([#1]) THEN "SELECT * FROM MYTABLE WHERE DATE >= ('[#2]','MM-DD-YYYY')"

 

ELSE "SELECT * FROM MYTABLE WHERE DATE >= ('[#2]','MM-DD-YYYY') AND CLIENT = " +[#1] Endif

 

It throws in a query error - not sure why? Attaching a debug screenshot with this. (the number [#3] in the debug is the other constraint on the date (i.e date <= ('[#3]','mm-dd-yyyy')

 

Since the original problem was solved, i will mark your reply as the solution, but appreciate a bit more help. 

 

thanks

Highlighted
7 - Meteor

this is the exact syntax:

 

 

DATE >= To_Date('[#2]', 'mm-dd-yyyy')

 

i may not be entering the [#2] in there correctly?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @S-9 I'm assuming if you updating a date you are using a date interface tool? The format may not be correct as Alteryx typically inputs dates as YYYY-MM-DD. If you could provide a mock workflow of this example like the example you shown as a picture in your original post that be easier to troubleshoot.

Highlighted
7 - Meteor

I tried switching the format. now i am using Date >= ('2020-06-15','YYYY-MM-DD') and i get an error attached beneath. 

 

in the update value, i am replacing 2020-06-15 with [#2] so in the action tool it becomes Date >= ('[#2]','YYYY-MM-DD') 

 

hope these images help to troubleshoot

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @S-9 the date filters should be passing in the date format "YYYY-MM-DD". So unsure what is wrong without have access to your database. A last suggestion is have a text input being updated by the interface tools and then feed into a Dynamic Input tool as you can update where clause or replace specific strings etc so might be a better approach for you.

Highlighted
7 - Meteor

the only thing i am changing inside my query is the edit below:

 

when i replace the [#2] and [#3] with actual dates the query runs perfectly without any errors. 

 

where Date >= To_Date('[#2]','YYYY-MM-DD')
and Date <= To_Date('[#3]','YYYY-MM-DD')

this is the condition in the original query, everything else is the same:

 

where Date >= To_Date('2020-06-05', 'YYYY-MM-DD')
and Date <= To_Date('2020-07-01', 'YYYY-MM-DD')

 

this is the error i get: 

(ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Highlighted
7 - Meteor

sorry i understand a complete query will be easier to understand, but unfortunately i am not at liberty to post it here. 

Labels