change/translate access function switch() in to CASE clause for pass-through query
Hy guys. I'm lost here. I had this multiple query cascade but then I changed my back end from native access to MySQL. And cascade of queries suddenly became way too slow. So I sat down and I'm trying to "translate/rewrite" them in to on big pass-through query with multiple sub-queries. Actually I had a great success and everything before this last step runs without problem and EXTREMELY fast :)! Hurray for MySQL!
But now I'm stuck in one LAST FINAL STEP :)!
What I got is:
SQLString = "SELECT T1.DATUM, T1.ID_SeznamURE, T1.ZasedeniTermini AS ZasedeniTermini, " & _
"(SELECT Avg(ZasedeniTermini) " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T " & _
"WHERE T.DATUM = T1.DATUM AND T.ID_SeznamURE BETWEEN switch(T1.ID_SeznamURE - T2.mini <= 2, T2.mini, T2.maxi - T1.ID_SeznamURE <= 2, T2.maxi - 4, true, T1.ID_SeznamURE - 2) AND switch(T2.maxi - T1.ID_SeznamURE <= 2, T2.maxi, T1.ID_SeznamURE - T2.mini <= 2, T2.mini + 4, true, T1.ID_SeznamURE + 2) " & _
") AS UrnoPovprecje " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T1 INNER JOIN (SELECT D.DATUM, MIN(D.ID_SeznamURE) AS mini, MAX(D.ID_SeznamURE) AS maxi FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS D GROUP BY D.DATUM) AS T2 ON T1.DATUM = T2.DATUM " & _
"ORDER BY T1.DATUM, T1.ID_SeznamURE;"
But since switch() is function that MySQL does not recognize I got an error.
So I searched internet and as I understand I should use CASE clause for same results. So I came up with that:
SQLString = "SELECT T1.DATUM, T1.ID_SeznamURE, T1.ZasedeniTermini AS ZasedeniTermini, " & _
"(SELECT Avg(ZasedeniTermini) " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T " & _
"WHERE T.DATUM = T1.DATUM AND T.ID_SeznamURE BETWEEN (CASE WHEN T1.ID_SeznamURE - T2.mini <= 2 THEN T2.mini " & _
"WHEN T2.maxi - T1.ID_SeznamURE <= 2 THEN T2.maxi - 4 " & _
"WHEN true THEN T1.ID_SeznamURE - 2 " & _
"ELSE BEGIN END) " & _
"END CASE) " & _
"AND (CASE WHEN T2.maxi - T1.ID_SeznamURE <= 2 THEN T2.maxi " & _
"WHEN T1.ID_SeznamURE - T2.mini <= 2 THEN T2.mini + 4 " & _
"WHEN true THEN T1.ID_SeznamURE + 2 " & _
"ELSE BEGIN END) " & _
"END CASE) " & _
") AS UrnoPovprecje " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T1 INNER JOIN (SELECT D.DATUM AS DATUM, MIN(D.ID_SeznamURE) AS mini, MAX(D.ID_SeznamURE) AS maxi FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS D GROUP BY D.DATUM) AS T2 ON T1.DATUM = T2.DATUM " & _
"ORDER BY T1.DATUM, T1.ID_SeznamURE;"
And now I'm really lost since I can't see any mistake and I still get an error. This time it says:
You have an error in your SQL syntax.; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS UrnoPovprecje FROM (SELECT c.DATUM, c.ID_SeznamURE, Count(c.ID_SeznamURE) A' at line 1
But I think problem is still in syntax or use of CASE clause. But hey, I could be wrong :)! I have no idea what could be going on here, so if any of you MySQL jedis could lend me some of your force and show me the path, I would be really grateful!
THANK YOU!