Help with SQL Syntax Error

[dimidimi]

I have tried the following using a couple of SQLLite utilities and both produced a result set. However when I insert in in the SQL portion of a custom report I get a syntax error

Code: Select all

 SELECT transcount.categname,
       transcount.subcategname,
       Sum(totalamounts.sumamounts) AS amounttotal,
       Count(*)                     AS counttotal,
       Sum(totalamounts.sumamounts) / Count(*)
FROM   (SELECT mo,
               categname,
               subcategname,
               nofentries
        FROM   [subcategory_v1] stab,
               [category_v1] ctab,
               (SELECT mo,
                       cid,
                       sid,
                       Count(*) AS nofentries
                FROM   (SELECT Strftime('%m', transdate) mo,
                               categid                   cid,
                               subcategid                sid,
                               transamount               amnt
                        FROM   [checkingaccount_v1]
                        WHERE  transcode = 'Withdrawal'
                               AND payeeid <> 15
                               AND categid <> -1
                        UNION
                        SELECT Strftime('%m', a.transdate) mo,
                               b.categid,
                               b.subcategid,
                               b.splittransamount
                        FROM   [checkingaccount_v1] a,
                               [splittransactions_v1] b
                        WHERE  a.transcode = 'Withdrawal'
                               AND a.payeeid <> 15
                               AND a.categid = -1
                               AND a.transid = b.transid)
                GROUP  BY mo,
                          cid,
                          sid) totalentries
        WHERE  stab.subcategid = totalentries.sid
               AND ctab.categid = totalentries.cid) transcount
       INNER JOIN (SELECT mo,
                          categname,
                          subcategname,
                          sumamounts
                   FROM   [subcategory_v1] stab,
                          [category_v1] ctab,
                          (SELECT mo,
                                  cid,
                                  sid,
                                  Sum(amnt) AS sumamounts
                           FROM   (SELECT Strftime('%m', transdate) mo,
                                          categid                   cid,
                                          subcategid                sid,
                                          transamount               amnt
                                   FROM   [checkingaccount_v1]
                                   WHERE  transcode = 'Withdrawal'
                                          AND payeeid <> 15
                                          AND categid <> -1
                                   UNION ALL
                                   SELECT Strftime('%m', a.transdate) mo,
                                          b.categid,
                                          b.subcategid,
                                          b.splittransamount
                                   FROM   [checkingaccount_v1] a,
                                          [splittransactions_v1] b
                                   WHERE  a.transcode = 'Withdrawal'
                                          AND a.payeeid <> 15
                                          AND a.categid = -1
                                          AND a.transid = b.transid)
                           GROUP  BY mo,
                                     cid,
                                     sid) totalentries
                   WHERE  stab.subcategid = totalentries.sid
                          AND ctab.categid = totalentries.cid) totalamounts
               ON transcount.mo = totalamounts.mo
                  AND transcount.categname = totalamounts.categname
                  AND transcount.subcategname = totalamounts.subcategname
GROUP  BY transcount.categname,
          transcount.subcategname  

Could anyone help here? Thanks!
Dimitrios

[Nikolay]

just add ; to the end of sql script

[dimidimi]

Very good! Thanks Nikolay! Heads back to researching about Lua and Templates. Any pointers on those would be really appreciated Thanks again!