While loop in SQL query

1

Hi there,

Is the 'while' function currently supported Metabase? When trying to run it, it doesn't appear to be supported.

For context, I'm trying to count the number of rows per month, where 2 dates inside the row sits either side of the month I'm iterating over.

To do this I am setting a new variable 'month' and want to run a while loop where I will count the number of rows where created_at and end_date are either side of 'month' then add an interval 1 month to 'month' and run again until 'month' is = date_trunc('month', current_date).

Any help would be much appreciated!

Thanks,

2

Hi @sam-baggott
I don't think the problem is the loop, but the variable.
Post "Diagnostic Info" from Admin > Troubleshooting, and the query and which database you're querying.

3

Thanks @flamber

I can't seem to locate the relevant log at the moment, but here is the query I'm trying to make:

select month_date, number_customers from
(
month_date = '2020-03-01'
while month_date <= date_trunc('month', current_date)
begin
select month_date, count(case when created_at < month_date and has_right_to_use_app = 'true' as 1 else null end) as number_customers from app_database.companies
month_date = month_date + interval '1 month'
end
)
group by month_date

For context, I'm still fairly new to SQL so if it is the query, any help would be much appreciated!

4

@sam-baggott Not asking for the logs, but that would be helpful too, asking for "Diagnostic Info" from Admin > Troubleshooting

  1. Diagnostic Info
  2. Which database type you are querying
5

Ah I see!

{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.173-106.229.amzn1.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"snowflake"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.1"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-04-27",
"tag": "v1.39.1",
"branch": "release-x.39.x",
"hash": "6beba48"
},
"settings": {
"report-timezone": "UTC"
}
}
}

it's a relational database I am querying.

Thanks,

6

@sam-baggott You are querying Snowflake.
So you would need to do something like this:
https://docs.snowflake.com/en/sql-reference/session-variables.html
But Metabase does not support multi-statement queries, so the only way to do something like that would be to create a View or Function on the database and have Metabase query that.
https://github.com/metabase/metabase/issues/4050 - upvote by clicking :+1: on the first post