Update SQL in Input tool using action and text box

[S-9]

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.

[JosephSerpis · Accepted solution]

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

[S-9]

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

[S-9]

this is the exact syntax:

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

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

[JosephSerpis]

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.

[S-9]

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

[JosephSerpis]

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.

[S-9]

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

[S-9]

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