'IN' operator taking a list of arguments

[miya]

The following SQL syntax is a valid one.

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
WHERE a.state IN ('CA', 'TX', 'AZ')
GROUP BY
  a.state
ORDER BY
  proportion ASC

How can I achieve the following?

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
[[ WHERE a.state IN ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

The focus is on the optional WHERE statement that should take a list of states (arguments).

Thank you.

[AndrewMBaines]

Nope, doesn't work. I had the same problem. I've now changed my dashboards from custom SQL to using views.
Views also make code reuse easier. If you're not able to create views in your database due to security, take a look at:

More SQL - how to create a view when you don't have admin rights

I’ve found that in 0.28 I’m limited when I use SQL Queries as it prevents me using multiple values in a filter. My solution so far has been to just use a view. Then I realised that the customer I’m working for is one of the few who would allow me to create views on their database, so had to come up with a new solution for future use. Answer is to use SQL Server Express with linked servers. I can then create views of the ‘proper’ database within a new database in Expess. On top of that, I then …

[lucasloami]

@miya to achieve a similar result, you just need to add a normal field filter to your question. The lastest versions of Metabase already offer multiple selection filter.

So, instead of

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
[[ WHERE a.state IN ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

just use

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
WHERE 1=1 
   [[ AND ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

[AndrewMBaines]

I've just realised where I've been going wrong! I've been using:

when I should have been using:

First option is just a straight text substitution whereas the 2nd does the correct SQL.

[Noah]

@lucasloami

WHERE 1=1 [[ AND ({{state}}) ]]

The "special sauce" in the example above seems to be () surrounding the double {}. Otherwise there is no difference compared to similar examples.

The syntax in the quote above behaves as expected, similar to SQL "IN", when linked by a field filter to an integer column, to process a default value of a space separated sequence of alphanumeric values such as:

123 456 789

However, when linked by a field filter to a varchar column, it doesn't behave the same with default value of a space separated sequence such as:

123456789 192837465 918273645 987654321

Any ideas how to get past that?