Is there a best practice for utilizing the array that's derived from a multi-select component on a "IN" clause with Apache Hive HQL? I'm utilizing the following query, but I'm getting an Unknown type array.node error when trying to use the bracket notation:
However, it does work when I turn the same exact discreet values explicitly into an array:
Could this be an issue of SQL Prepared Statements?
Hi @Douglas_Urriola,
Does one of the two MS SQL options work here? 
Disabling prepared statements may work, but we generally suggest finding a solution with prepared statements enabled if at all possible 
I don't have a Apache Hive HQL resource to test with, so this exact syntax may not work, but it may lead you in the right direction. For my Databricks resource, I can use where id in (select explode(array("1","2"))) but then need the below syntax when referencing the array of values dynamically:
The latter query works, while the former does not.
Yeah, unfortunately the sequence of functions you're using for your Databricks table isn't working for my Hive table:
I've tried a multitude of potential solutions and have to found a solution.
sorry to hear! I see one customer was able to use something like this:
WHERE regexp_like( {{JSON.stringify(multiselect.value)}}, columnName)
Happy to take a look with you in office hours if you'd like!
Yeah, unfortunate that function seems exclusive to Oracle-based databases. Yes, I'll try to join tomorrow's office hours meet. Thanks!
Hey @Douglas_Urriola - It does look like an issue with the array type specifically, was able to work around it with a local Hive instance by casting the multi-select array to a string and then back in the DB with something like
where array_contains(split({{ multiselect1.value.join() }}, ','), COLUMN)
Hi Justin,
This is working like a charm.
Thanks so much to all of you guys for your help!!!
