Using Multiselect to query my table

Hi guys, I have a database that stores all orders received on my e-commerce website. On the Orders Table, each row has a column named "Status," and the status column is of the Char (1) datatype. I created a stored procedure that creates an Order Summary and groups orders by order date and order status. I am trying to use Multiselect to filter my table by date and status. But the multiselect keeps parsing my Order Status as "A", "C" into my query, but SQL's IN() function does not accept the string "A" and "C" How do I make Retool pass the selected data as a single letter and not a string?

1 Like

Hi @Makcsd We have some strategies documented here: https://docs.retool.com/docs/sql-cheatsheet

Let me know if that doc helps! :crossed_fingers:

2 Likes

Hello everyone,

I'm briefly dropping in to confirm that @Tess provided a working answer. I've been struggling for the past two days with the exact same problem that @Makcsd has: I use Retool Database. In one of the tables called director_items I have a column named status of type enum with two string options: "Open", "Archived". In my app, I have a multi-select component (named statusFilter_multiselect) with these exact two values: "Open" and "Archived".

I wanted to get an SQL query to the Retool Database to return all the rows that match one or more of the options selected in the statusFilter_multiselect component.

Using the SQL cheatsheet provided by Tess I got the following SQL to work as expected:

The [SQL cheatsheet](SQL Cheatsheet - best practices for different flavors of SQL syntax - Retool Tips & Tricks - Retool Forum 40) provides just an example for an array of integers (not strings, in my case) so I didn't think to check it with strings as well. I was trying different "string_to_array" functions to get the same result, assuming that strings won't work with the ANY function. Thankfully, this thread motivated me to give it a try and it worked.

Thanks for your help, guys. :blush:

2 Likes

Thank you for sharing, @emanuelsan! Glad your query is working :tada: