Dynamically Update SQL Code for Input

Question

Hi,

I want to build an input where the "where" function can not only be modified but also additions/subtractions can be made. For example, if we are pulling data from our trial balance, we want to be able to filter (but not required):

1) Year

2) Account

3) Functional Currency

So our current code (slightly modified for simplicity) is:

where a.acct = '100000'
and a.period_year = 2022

But we want to have the option to remove these so it could just be:

where a.acct = '100000'

or have the option to add to it so it could be:

where a.acct = '100000'
and a.period_year = 2022

and a.func_currency = "USD"

Is this possible? If so, what would the approach be?

Christine_Assaad · Answer

Hello @adam_rafael_18

You can make your SQL query dynamic as mentioned by @apathetichell . You can do this using regular in memory tools as well as INDB ones, depending on your data and use case.

In the example below, the text tool has a query and alias. Then a formula tool was used to append to the query or even replace it.  After that, you can  send the data to the "Dynamic input in-DB "to query your DB OR  you can use a dynamic input to replace a dummy query with the one you just built.

Hope that helps! Cheers!

apathetichell · Answer

Yeah. I do this. I'd use combo of interface tools/append tools, a summary tool to concatenate the query and finish it with a dynamic connect in-db...

The exact "how this all works" depends upon your database and your specific use-case - but the short is yup. Totally doable.