Dynamic SQL Query with Regex to replace year

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

Regex

Help

Accepted answers

Felipe_Ribeir0

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.

Felipe_Ribeir0

Here it is working fine.

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

I can update this 2022 by any year.

All comments

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.