Failed to use CASE statement (THEN is not recognized)

[tongyu]

I have a Grafana + MariaDB setup to get the total throughfeed time in a factory. In this dashboard I want to compare two total througheed time(average time of each process added together for product Seiten & Boden) and only show the longer one as final throughfeed time. I want to use CASE WHEN statement for this purpose, however, THEN seems not recognized (not in blue like CASE but in black) and I got error 1064.

Below is part of my query(I have many sub-queries to get the average time of each process,here only show the first one) :

SELECT
Dwell_P_BS1 + Dwell_Kom + Dwell_ABL + Dwell_P_ABL + Dwell_P_Montage AS “Total Seiten”,
Dwell_Kom_Boden+Dwell_P_Kom_Boden+Dwell_KAM_Boden+Dwell_P_KAM_Boden+Dwell_Montage_Boden AS “Total Boden”

CASE
WHEN (Dwell_P_BS1 + Dwell_Kom + Dwell_ABL + Dwell_P_ABL + Dwell_P_Montage) > (Dwell_Kom_Boden+Dwell_P_Kom_Boden+Dwell_KAM_Boden+Dwell_P_KAM_Boden+Dwell_Montage_Boden)
THEN (Dwell_P_BS1 + Dwell_Kom + Dwell_ABL + Dwell_P_ABL + Dwell_P_Montage)
ELSE (Dwell_Kom_Boden+Dwell_P_Kom_Boden+Dwell_KAM_Boden+Dwell_P_KAM_Boden+Dwell_Montage_Boden)
END AS “Total”;

FROM
(SELECT
avg (ML_Dwell_Time + ML_Transport) AS “Dwell_P_BS1”
FROM TAB_MaterialLocations
WHERE
ML_MaxStatus = “9” AND ML_MaxContainer > “49” AND
ML_Closest_Device = ‘BU_SP116 - Lagerplatz 2 BC1 Halle 6’ OR ML_Closest_Device = ‘BU_SP123 - Lagerplatz 1 BC 1 Halle 6’ AND
ML_Part_Type = “40001” AND
ML_Dwell_Time > “0” AND
$__timeFilter(ML_Latest_Date)) y,

THANK YOU!!

~ the grafana team

[alexandrearmand]

Hello,
You can try your query in Mariadb before using it in grafana,
to achieve that you can use query inspector in grafana to get the exact string send to Mariadb

I Notice you have an “;” character at the end of your case statement, does this not end the query ?

[tongyu]

Hi Alexandrearmand,

Thank you for your help! I will try it now in MariaDB.

There are still sub-queries after “;” actually. I thought end means just the end of the case statement.