Problems with a multiselect returning a NULL or empty array

Queries and Resources
1

Hi, I'm very new to Retool and SQL so be gentle!

I've got a table being populated from a MySQL database and that all works fine.
I need to filter the table using a Multiselect. Everything works but ONLY if there is something selected in the Multiselect. If nothing is selected I get an SQL error.

My query is:

SELECT *
FROM mytable mt
WHERE ({{ sel_multislect.value }} = '' OR {{ sel_multislect.value }} IS NULL OR mt.myrow = {{ sel_multislect.value }})

If do not chose an option from the multiselect I get the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IS NULL OR s3.Shell_Style = )' at line 3"

It appears the query is not handling the situation where the sel_multislect.value is nothing, how can I fix this?

Many Thanks :slight_smile: MIke.

2

Welcome to the forum!

Try

SELECT *
FROM mytable mt
WHERE ({{ !sel_multislect.value }} OR mt.myrow = {{ sel_multislect.value }})
3

Hi Scott, thanks for the welcome and the prompt reply.

I've tried your query, and get a slightly different error:

'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 3'

I've also noticed it doesn't work when selecting more than one option, it errors with:

'Operand should contain 1 column(s)'

So, so far my multiselect only works when selecting one option lol

4

Yes ok
So

SELECT *
FROM mytable mt
WHERE {{ !sel_multislect.value }} OR mt.myrow = ANY({{ sel_multislect.value }})
5

Same error..

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 3

And if I try to select 2 options from the multiselect..

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''26', '21' )' at line 3

6

OK I don't know much about MARIA DB but you can try replacing the = with <> before ANY
Check out

1 Like
7

Hi @MadMiniMike, were you able to sort out this query? This post might have some helpful examples