"Unrecognized name: nil" when using "default: all"

I have some issues with my query..

The report has one query for parameters which looks like this:

select
  common_name as name,
  iso_3166_1_2_letter_code as code
from
  [metadata.country_metadata]
order by 1

{% form %}

country:
  type: multiselect
  default: all
  description: Ordered by country code
  options:
    labels: name
    values: code

{% endform %}

And then I use that one in this query:

select
  count(case when days_active.today > 0 then 1 end)
from
  `users.users_20190109`
where
  country in ({{ country }})

When I run this I get the following error message:

Unrecognized name: nil at [19:192]

It's pretty weird, because I always get [19:192] no matter how long the query is, so it seems like the issue is in the parameters query, even though it follows your example in this article: https://help.modeanalytics.com/articles/parameters/#dynamic-options-list

If I change "all" to for example "SE" in the parameters query, no problem occur.

What is the issue?

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • After a while me and my colleague solved the issue.

    Apparently: 

    select
      common_name as name,
      iso_3166_1_2_letter_code as code
    from
      [metadata.country_metadata]
    order by 1

    resulted in one row with a `name`, but without any value for `code`.

    So in the future we need to remove the "British Sovereign Base Areas" because they apparently do not have a country code....

    Reply Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 2 mths agoLast active
  • 1Replies
  • 50Views
  • 1 Following

Create A New Discussion

Share your thoughts