This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Soon after posting this I thought of a work-around (or maybe it's the intended approach).
My original approach was trying this:
Text Box--> Action --> Input Oracle OCI Sql --> Output to Excel
The solution that worked was:
Text Box --> Action --> Input Text --> Formula --> Dynamic Input --> Output to Excel
Still seems like I should be able to pass a transformed input value as a parameter into my sql statement using my first approach, but the second approach does appear to work in a straightforward manner.
@Patrick_digan Thanks I'm still learning how the action tool works, and I see your solution would do the trick. I think the trade-off is maybe the comfort with doing complex formulas in the action formula vs just using a generic approach that also supports null checks and then passing that to a text input and then a formula tool to do the comma-separated list. The latter gives more flexibility to evaluate/modify/etc. the input values as opposed to having to do it all in one action formula.
So, in my case, I need to do what I mentioned about formatting the comma-separated list, but I also need to manage nulls. So use this in my action formula to handle nulls:
if isempty([#1]) then replace([Destination],"[text input field value]","nullentry") else [#1] endif
This lets me control null/optional fields as well in my approach. I think use logic to transform "Nullentry" appropriately in my replace string for the sql statement.
Overall, it's good to see the flexibility, and I'm learning to think differently in how to flow this out in Alteryx vs a mindset using Oracle pl/sql, etc.
I am trying to do the same thing except my data field in my sql is an integer.
An associate gave me this to use but it is not working:
replace([Destination], "And Load_id In (0) ", "And Load_id In ("+REGEX_Replace(([#1]), '\n', ",") + ")")
I get results, but just for load_id 0.
I am replacing 0 in my sql with a list of Load id's that the user enters.
Load_id is an integer in the source data.
Here is my sql
SELECT "LOAD_DIM"."LOAD_ID","LOAD_DIM"."FRT_MOVE_CATG_CODE","EDI_MSG_TRACKING_FACT"."MSG_EVENT_TS","EDI_MSG_REASON_DIM"."MSG_REASON_CODE" FROM "US_TRANS_DM_VM"."LOAD_DIM","US_TRANS_DM_VM"."EDI_MSG_TRACKING_FACT","US_TRANS_DM_VM"."EDI_MSG_TYPE_DIM","US_TRANS_DM_VM"."EDI_MSG_REASON_DIM" WHERE current_ind='Y' AND "LOAD_DIM"."LOAD_SK_ID" = "EDI_MSG_TRACKING_FACT"."LOAD_SK_ID" AND "EDI_MSG_TRACKING_FACT"."MSG_TYPE_SK_ID"="EDI_MSG_TYPE_DIM"."MSG_TYPE_SK_ID" AND "EDI_MSG_TRACKING_FACT"."MSG_REASON_CODE_SK_ID" = "EDI_MSG_REASON_DIM"."MSG_REASON_CODE_SK_ID" AND "EDI_MSG_REASON_DIM"."MSG_REASON_CODE" IN ('AF','AI','AJ','AY','A1','AM','AG','AO','AV','BB','AL') And Load_id In (0)