How to input multiple values for Number filter in MySQL

I am trying to input multiple order numbers in a variable, and show results from a table.

Here's the code:

Select ordnumbh, status

From order_header

Where {{Ordnumbh}}

When I put ordnumbh = 0000 (where 0000 is a number that exists in the ordnumbh table, I get a result, when i map ordnumbh to the field (configured both as a caterogry and as a number in two separate attempts), I get no results.

Ideally, I want to enter multiple order numbers and see if the query returns results corresponding to these order numbers

Diagnostic Information:

{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_262-b10",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_262",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.262-b10",
"os.name": "Linux",
"os.version": "4.18.0-193.el8.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"mysql",
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.10"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.36.4",
"date": "2020-08-17",
"branch": "release-0.36.x",
"hash": "196c1f6"
},
"settings": {
"report-timezone": "Asia/Calcutta"
}
}
}

Hi guys, I've the same problem but for numbers.

What can I do?

@marvin_suprol Post your query. If you use a Number variable, then you can do this:
where number_column in ({{input}})

I am trying this, but this keeps returning no results for me.

@marvin_suprol

  1. Post your query, and a screenshot of the variable sidebar open.
  2. Post "Diagnostic Info" from Admin > Troubleshooting.
  3. Are you sure that the column is integer?

Here's the post I just created. Thanks for offering to help. Adding the info you asked for.

The column is indeed an integer - i checked

Here's the diagnostic info:

{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_262-b10",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_262",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.262-b10",
"os.name": "Linux",
"os.version": "4.18.0-193.el8.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"mysql",
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.10"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.36.4",
"date": "2020-08-17",
"branch": "release-0.36.x",
"hash": "196c1f6"
},
"settings": {
"report-timezone": "Asia/Calcutta"
}
}
}

Hi @marvin_suprol
You are not using Number filter. You are using Field Filter.
What is the column type of order_header.ord_numb_h?
You can use Number filter by doing this: How to input multiple values for Number filter in MySQL

I would recommend that you upgrade Metabase, latest release is 0.40.3.1 (remember to backup first):
https://github.com/metabase/metabase/releases/latest
And update Java:
https://www.metabase.com/docs/latest/operations-guide/java-versions.html

You're right, the screenshot shows the variable is a field filter.

Even when I tried (before this attempt) to use a number filter, and I used the code:

Where ordnumbh in ({{Variable_As_Number_Filter}})

I was getting zero results, regardless of whether I enter one order number in the variable, ro multiple:

To enter multiple orders, I've tried multiple approaches:

a. 000,111,222
b. '000','111',

@marvin_suprol Check the query log on MySQL to see what it is using to run the query.

You cannot control parameter types - that is handled by Metabase for security reasons, so you cannot input quotes to escape.
And if the column is really an integer, then there is no 000 - that would just be 0, unless ZEROFILL

Which version of MySQL?

000 was a mere example to indicate number. I understand I caused you more confusion than clarification.

Thanks for your patience.

Here's three screenshots explaining

  1. What is working for me is if I assign the variable as number, and search for only one order number at a time

  2. What is not working for me is if I use the 'in' function

  3. What is also not working for me is trying to search for multiple orders using the 'in' function

I need to achieve the 3rd. How can I do that?

@marvin_suprol It's not working because the syntax is incorrect. You need to use parentheses.
where column in ( {{variable}} )