Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Update SQL in Input tool using action and text box

S-9
8 - Asteroid

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.

8 REPLIES 8
JosephSerpis
17 - Castor
17 - Castor

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

S-9
8 - Asteroid

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

this is the exact syntax:

 

 

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

 

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

JosephSerpis
17 - Castor
17 - Castor

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

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
17 - Castor
17 - Castor

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

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

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

Labels