Dynamic SQL Query with Regex to replace year

[Link86]

I have the query

select * from table

where fy = 2022 and cy = 2022 and month = 1.

I am making an analytic app and I want to be able to change the year with a formula. I am using the text input tool connecting to the event tool. In the event tool configuration I am doing the following:

Select an action type: Update Value with Formula

Value or Attribute to Update: File - value='aka: Connection|||select * from table...

Formula: REGEX_Replace([Destination], '/[0-9]{4}', [#1])

I have tested my regex at regex101.com and I don't seem to have a problem there. However, when I run this app, it does not change the dates in the query. I am not sure if this helps, but we are using Oracle SQL.

Any help would be great.  Thank you

[Felipe_Ribeir0]

Hi @Link86 

To replace a specific character inside a query, you can use the same logic applied on the post bellow:

Solved: Re: SQL: IN Statement (1000 limit Oracle) - Alteryx Community

[Link86]

@Felipe_Ribeir0 This post was not helpful as it does not work for me but thank you for trying. Also, I am not sure how a dynamic input tool is going to help me here. The dynamic input tool requires both an input and an output. I am using the green input tool to get the data from the database. However, I need users on the gallery to be able to change the date in the query before the data is pulled which makes this an analytic app. I am looking to do it the way of regex unless it is absolutely not possible because this limits the number of tools which speeds up the workflow.

Thank you

[Felipe_Ribeir0 · Accepted solution]

Ok, it is not exactly about the used components, but about the logic.

Anyway, if you want to replace just the 2022

Why dont you do something like this? It will replace the 2022 by any provided year.

[Link86]

@Felipe_Ribeir0 placing just a year for the formula results in an error because it wipes out the SQL all together.

[Felipe_Ribeir0 · Accepted solution]

Here it is working fine.

My query is select * from table where year = '2022'

I can update this 2022 by any year.

[Link86]

@Felipe_Ribeir0  it looks like i need to have my years as strings in the query itself. I had them as numeric. This works now. Thank you for your help.

Thank you