#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    1
    Rep Power
    0

    Unhappy What could be wrong with this 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 into one 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 had 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 the 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!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    could you do us a favour and post only clean sql, without all those quotes and ampersands

    ideally, you should test your clean sql directly in the mysql engine, before embedding it in your perl code or whatever that is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo