I’ve only been working with Redash for a couple months so maybe the solution is easy. I keep getting a SQL error for this query even thought everything seems correct. If I change the syntax of the query the error goes away but the parameters no longer function. Is there a way to get parameters to be both multi select and default to all? Thanks!
select
sum(event_count) AS “Number of Events”,
count(distinct name) AS “Name”
from viz_counts
WHERE
ts >= ‘{{ myDate.start }}’ AND
ts <= ‘{{ myDate.end }}’ AND
(‘ALL’ IN ({{ sname }}) OR s_name IN ({{ sname }})) AND
(‘ALL’ IN ({{ mem }}) OR program IN ({{ mem }}))
SELECT ‘ALL’ AS sname
UNION ALL
(SELECT DISTINCT s_name FROM viz_attrib
WHERE s_name != “” )
Error running query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALL) OR site_name IN (ALL)) AND (‘ALL’ IN (ALL) OR oem_program IN (ALL))’ at line 8
This setting is selected which is why this is so frustrating. Our data engineer thinks Redash is caching the variable ALL wrong but I’m unsure of this.
Look very closely at the error from your database. You haven’t posted your actually query so it’s not possible to debug further on the forum. But the error shows a quotation mark that is opened and not closed. Fix it and your query will work. Nothing else we can do on the forum.
I have pinned down the error in question and I believe it’s a bug in the application of the single quote function in the parameter settings. When using this combination of queries to create multi-select parameters in Redash that default to ALL I get the following sql error:
SELECT ‘ALL’ AS value
UNION ALL
(SELECT DISTINCT set_value FROM table
WHERE set_value != “” )
(‘ALL’ IN ({{ value }}) OR set_value IN ({{ value }}))
error message: Error running query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALL) OR value IN (ALL)) AND (‘ALL’ IN (ALL) OR value1 IN (AL’ at line 21
To correct the error we added in the missing single quotes around the ‘ALL’ and the sql query functioned correctly when loading the dashboard and using the multi-select filters.
SELECT “‘ALL’” AS value
UNION ALL
(SELECT DISTINCT set_value FROM table
WHERE set_value != “” )
HOWEVER when switching from multi-selection back to ‘ALL’ in the filters we then receive this sql error:
Error running query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALL’’) OR value IN (’‘ALL’’))’ at line 9
It appears as though the single quotes are not applied to the default filter parameter ALL when the dashboard is loading causing an error. But if we put them in as a default and use the filters, when we go back to ALL the single quotes in the filter setting are now applied causing another error. Is there a way to get the single quotes in the parameter settings to be applied consistently even to the default value? This seems like a bug, the query won’t work if the single quotes aren’t applied to the default value in the parameter settings.
I don’t think that is the issue. I checked the format and in Redash we are using the straight quotes. It seems like this forum might have auto edited and changed them when I copy pasted. So it is another issue, but not that.
How peculiar. What happens if you try this completely independent of another WHERE clause? If you create the same parameter in another query does it appear there also?
For example:
with base as (
SELECT 'a' "col"
UNION
SELECT 'b' "col"
UNION
SELECT 'c' "col"
)
SELECT "col" FROM base WHERE 'all' in ({{ param }}) OR "col" IN ({{ param }})
