advanced query error

advanced query error

  
hi there when i try to publish my advance query it returns this message. the problem is when i tried the test button located in outsystems to test the advance query it returns the right output

Error in advanced query SQL1 in Preparation in WebScreen1 in MainFlow in Envirosite (SELECT falsecount.USERTEMPID,(COALESCE(truecount.STATCOUNT,0) + COALESCE(QAAuditTruecount.STATCOUNT,0)),  (COALESCE(falsecount.STATCOUNT, 0) + COALESCE(QAAUDITFalsecount.STATCOUNT,0)),   (COALESCE(truecount.STATCOUNT, 0) + COALESCE(falsecount.STATCOUNT, 0)+ COALESCE(QAAUDITFalsecount.STATCOUNT,0)  + COALESCE(QAAuditTruecount.STATCOUNT,0)) AS TOTALCOUNT  from       (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT       from {BPAChecklist}      INNER JOIN {LLSQLUpdate}          ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]      INNER JOIN {QAChecklist}          ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate] AND {QAChecklist}.[IsPass] = false      GROUP BY {BPAChecklist}.[UserID]      ) falsecount    left join         (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT      from {BPAChecklist}      INNER JOIN {LLSQLUpdate}          ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]      INNER JOIN { ...): 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 '' at line 1

regards

Not sure, because I don't see the whole sql-query.

but my guess, based on the error, you do something with a variable that you expand-inline.
test-query will succeed because you use actual values.
runtime, it might be empty and then certain queries will end up as an error.

im using this sql code on the advanced query


SELECT falsecount.USERTEMPID,(COALESCE(truecount.STATCOUNT,0) + COALESCE(QAAuditTruecount.STATCOUNT,0)),
(COALESCE(falsecount.STATCOUNT, 0) + COALESCE(QAAUDITFalsecount.STATCOUNT,0)), 
(COALESCE(truecount.STATCOUNT, 0) + COALESCE(falsecount.STATCOUNT, 0)+ COALESCE(QAAUDITFalsecount.STATCOUNT,0)
+ COALESCE(QAAuditTruecount.STATCOUNT,0)) AS TOTALCOUNT
from

   (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT 
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate] AND {QAChecklist}.[IsPass] = false
    GROUP BY {BPAChecklist}.[UserID]
    ) falsecount

left join 

    (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate]   
    INNER JOIN {ForEasyFix}
        ON {ForEasyFix}.[QAChecklist] = {QAChecklist}.[Id]
    INNER JOIN {QAAudit}
        ON {ForEasyFix}.[Id] = {QAAudit}.[ForEasyFix] AND {QAAudit}.[IsPAss] = False
  
        -- WHERE datefinish between dateparam1 and dateparam2
    GROUP BY {BPAChecklist}.[UserID]
    ) QAAUDITFalsecount

on falsecount.USERTEMPID = QAAUDITFalsecount.USERTEMPID

left join 

   ( select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate] AND {QAChecklist}.[IsPass] = true
        -- WHERE datefinish between dateparam1 and dateparam2 
    GROUP BY {BPAChecklist}.[UserID]
    ) truecount

on truecount.USERTEMPID = falsecount.USERTEMPID
left join 

    (select {BPAChecklist}.[UserID] USERTEMPID, Count({BPAChecklist}.[UserID]) AS STATCOUNT
    from {BPAChecklist}
    INNER JOIN {LLSQLUpdate}
        ON {BPAChecklist}.[Id] = {LLSQLUpdate}.[BPAChecklist]
    INNER JOIN {QAChecklist}
        ON {LLSQLUpdate}.[Id] = {QAChecklist}.[LLSQLUpdate]
    INNER JOIN {ForEasyFix}
        ON {ForEasyFix}.[QAChecklist] = {QAChecklist}.[Id] 
    INNER JOIN {QAAudit}
        ON {ForEasyFix}.[Id] = {QAAudit}.[ForEasyFix] AND {QAAudit}.[IsPAss] = True
  
        -- WHERE datefinish between dateparam1 and dateparam2
    GROUP BY {BPAChecklist}.[UserID]
    ) QAAUDITTruecount

on falsecount.USERTEMPID = QAAUDITTruecount.USERTEMPID
ah, remove the comments at least.

in my experience, comments are buggy in runtime-mode
(don't ask me why :) )




Hi Joshua,

If you want to put comments inside your query use this format /*XXXXX*/ instead and it will probably work (not sure about MySQL though as I haven't used it yet).

Cheers
Guilherme
hi guys

just tried to change the way i commented out the where clauses and it worked. it seems that when we use the "--" to comment out it gets a runtime error but if we use the format that Guilherme gave it works perfectly

thanks for the responses guys
Josh