Use variable in SQL CASE inside WHERE

[aaron3779]

Hi!
I’m trying to use a variable to validate a CASE inside WHERE like this:

SELECT timestamp AS "time", value AS "average"
FROM average_values
WHERE lookback =
  CASE
    WHEN ${lookback:raw} = "12h" THEN "average_last_12_hours"
    WHEN ${lookback:raw} = "7d" THEN "average_last_168_hours"
    ELSE ""
  END
ORDER BY timestamp;

The variables are set up as “Custom” type currently. I tried “Interval” as well.
Looking at the issue I have with the query it seems I don’t fully understand how the variable values are represented in the SQL query.
How can I use a variable in a CASE statement properly?

[svetb]

Hey @aaron3779 welcome to the forum.

Can you share some more info? You mentioned “the issue I have with the query”, but you have’t shared what it is? Are you not generating a valid SQL query (and thus getting a SQL error), or are you not getting data, or are you getting incorrect data, etc?

You can use the query inspector to see the actual query that’s being sent to the server, and the raw response - maybe that would help.

[aaron3779]

Hi @svetb , thanks for getting back.
This is the error message I’m getting:
Error 1054: Unknown column '12h' in 'where clause'
Based on the error I think the SQL query is valid but trying to access a wrong column, i.e. a column with name “12h”. Whereas it should access the “lookback” column where the column values are “average_last_12_hours”.
I’m trying to achieve a query that would result partly in:
WHERE lookback = "average_last_12_hours"
given the ${lookback} variable is 12h.

[svetb]

Ok, thanks for clarifying. You didn’t paste your full query, but I’m guessing it includes something like WHEN 12h = "12h", which is not valid SQL (unless you indeed have a column called “12h”).

I’m not a SQL expert, but I think that if you did "12h" = "12h" that might work in a WHEN statement. To get that, try ${lookback:doublequote} (see Advanced variable format options | Grafana Labs) or "${lookback}".