SQL select using parameter returning only the input

Need help Plugins
1

I am trying to load data from the SQL connector using parameters to dynamically change the searches. This works when using the parameters to add a filter for a Time column, but when I try to use a parameter to determine which column is included in the Select operator, only the data sent as the parameter is returned. Writing as one line returns the same issue.

Here is the SQL query I have tried. Writing the query without the first parameter works as expected.

46
46770×482 35.6 KB

The value returned is only the data passed to the query as the first parameter (USA, in this case).

26
26998×438 6.64 KB

Here is what it should look like, and what it looks like when writing in the USA value instead of using a parameter.
02

Any help would be appreciated.

2

I don’t believe dynamic columns names have the same syntax as dynamic values in sql. You might do some google searches for the type of sql server you are using to see what they recommend for syntax, and then apply to your bubble query. Or hopefully an sql expert will chime in here!

3

The query needs to specify which columns to return. At the moment the query says “take this value passed in: USA, and display it with a column name of selectedcountry”.

What did you want the first parameter to do?

Maybe you wanted to have:
SELECT selectedcountry
FROM …

4

The alias of selectedcountry is created in the query so as to have a consistent value to reference in the setup of the various metrics on the site. I am looking to specify a country (listed as USA in the example but could be a list of 10 others) and call for just that specific country column in the SQL database.

The SQL table does work if I just reference the column, but it’s a lot of data to pull in and therefore slows down the page.

5

Here’s how you can limit the rows to a particular country

SELECT …
FROM …
WHERE Country = ?
AND (Timestamp between ? and ?)

Your first post mentioned choosing different columns depending on a parameter?

Can you give more context, some example to show what you want?

6

Each of the countries are listed as a separate column, so I am attempting to choose which column to bring in based on the parameter. There isn’t a country column with all of the countries listed.

Here is what the table looks like. Based on a dropdown on the page, I am hoping to determine which country column to pull in with the SQL query. Hopefully that helps.

36

7

That’s a messed up data structure!

You can do this, naming every country column …

SELECT CASE 
WHEN tc = 'USA' THEN `USA`
WHEN tc = 'United_Kingdom' THEN `United_Kingdom`
WHEN tc = 'Argentina' THEN `Argentina`
WHEN tc = 'Brazil' THEN `Brazil`
END AS countrydata
FROM tabilize, (SELECT ? tc) t
LIMIT 200

If you have access to create a stored procedure, you can do it with a dynamically created query, without having to name every column.

8

Thanks, Mishav! Very helpful.

Is it possible to use multiple values for the country filter, potentially with an any/or function here (SELECT ? tc) ?

9

Yes, first I’d unpivot your table into three columns of Timestamp, Country, Data. It would be appropriate to create a view, if you have permissions.

Then constrain by matching a comma separated list of countries.

SELECT Timestamp, Country, Data FROM (
  SELECT Timestamp, 'USA' Country, `USA` Data FROM tabilize UNION ALL
  SELECT Timestamp, 'United_Kingdom' Country, `United_Kingdom` Data FROM tabilize UNION ALL
  SELECT Timestamp, 'Argentina' Country, `Argentina` Data FROM tabilize UNION ALL
  SELECT Timestamp, 'Brazil' Country, `Brazil` Data FROM tabilize
) countriesdata
WHERE FIND_IN_SET(Country, ?) > 0
LIMIT 200

The parameter is a text made from joining a list of country names with a comma.

The table name ‘tabilize’ suggests that there is a version of this data that hasn’t got the data pivoted, it’d be a lot easier to use that instead.