Failed to use CASE statement (THEN is not recognized)

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!! :slight_smile:

~ the grafana team

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 ?

1 Like

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. :sweat_smile: