How to add a date prompt to an Oracle database connected query

[jmeece9]

I have successfully connected to my Oracle Database in Microsoft Access to Alteryx, however, I cannot figure out how to run the query I created in Alteryx as of a specific date in time.  I have tried using the Date tool along with the Dynamic Input tool, but I can't seem to get the query to run properly, so I believe I'm missing either another tool, or I don't have the information configured correctly.  Any help is appreciated.

[RishiK · Accepted solution]

Hi @jmeece9 can you post your workflow without the data?  I can have a look at what you have done so far.

You should be able to do one of the following for a given date in time:

- Query your database for a given date within the Input tool itself

- Use the Filter tool 

- Use the Interface tools to allow the user to input the date on the Filter tool 

[danilang]

Hi  @jmeece9 

You could try a technique like this.

The SQL statement is "Select * from Data t where t.AsOf = to_date('2019-01-01','yyyy-mm-dd')" and the input to the Dynamic Input tools contains a field called AsOfDate which = '2019-08-01'.  The Dynamic Input is configured to replace a specific string in the SQL Query with value from the ASOfDate field

Dan

[jmeece9]

Thank you - attached is the workflow.

[RishiK]

@jmeece9 

Did you manage to get it working based on the above recommendations?

[jmeece9]

Yes!  The piece I was missing was probably the simplest piece - I had to update the criteria in my query to "=date '2019.06.30'.  I will upload my updated workflow in case it helps someone else in the future.

Thank you for your help!