Passing a User Constant into an In-Database Flow

[alexnajm]

Good afternoon all!

I got an interesting question today - do y'all think it's possible to pass a User Constant as a "field" in an In-Database workflow?

For example, a Filter In-DB tool could have "O_ORDERDATE" >= '2024-08-01'... do y'all think it's possible to make it like "O_ORDERDATE" >= '%User.MyConstant%'?

The app option of using a Date interface tool definitely works (and using a Data Stream In tool followed by a Join In-DB tool on the date fields could probably work), but curious if there is another option without creating an app 😊

Thanks all!

[apathetichell]

my recommendation is always DYNAMIC INPUT IN-DB. In that case you'd have a data like '2024-08-01' in your base query in text - you'd use summarize tool to get that into one row. you'd use formula tool to replace '2024-08-01' with  the value of '%User.MyConstant%' and then you'd feed it into in-db.

[alexnajm]

Ah I like the idea @apathetichell - in this case then if the Filter In-DB is embedded in the middle of the In-DB workflow, would you get the query via a Dynamic Output In-DB tool, do this insertion logic, and use that updated query with the Dynamic Input In-DB?

I am going to try this out later today too

[alexnajm]

Turns out Dynamic Input In-DB tools don't like the WITH clauses that the In-Database tools create by default :/ without using some sort of Formula to reform the query, any thoughts @apathetichell ?

[apathetichell · Accepted solution]

hmm... I think we need to look at another way. I was going to recommend that you use a non-specific sql statement like select 'myvalue' as test - and bring that in for your join - but Alteryx won't support it.  I'd probably recommen creating a temp table for a join and then joining off of it (ie datastream in)...

also is the value a distinct value you are looking for (ie would it work in a join) or is it a filter comparison value?  it can't go in the initial query? if it's the former you'd need to join a new table. if it's the later you'd need to create a new table, join on dummy values (1=1) - and then filter for your value.

[alexnajm]

Yeah good call - I definitely think that would work and I thought of this initially, but the person wanting this doesn't have Create rights to use the DataStream In tool.

I'll pass the second part along too and see what may come about - thank you for your suggestions @apathetichell !