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.
Solved! Go to Solution.
Hi @S-9 I mocked up a workflow let me know what you think?
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
this is the exact syntax:
DATE >= To_Date('[#2]', 'mm-dd-yyyy')
i may not be entering the [#2] in there correctly?
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.
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
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.
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
sorry i understand a complete query will be easier to understand, but unfortunately i am not at liberty to post it here.