2 Replies Latest reply on Apr 2, 2015 7:52 AM by philmodjunk

    ExecSQL struggle

    Stu412

      Title

      ExecSQL struggle

      Post

      Hi there

      I'm struggling with an ExecSQL query at the moment which is frustrating as all my other queries in other areas are working fine.

      I have some data I want to summarise and sum up by customer, and by year.  An example of the data on the table is:

      CusID | Group | Value

      1010 | 65 | 1000

      1010 | 65 | 2000

      1010 | 66 | 1400

      1010 | 66 | 600

      My query is this:

      ExecuteSQL ("
       
      Select Sum(Value) from ThisTable where 

      Group=? and
      CusID=? 


      ";

      "";"";

      65;
      CusID_PK_CusTable

       
      )

      All I'm getting is question marks at the moment which suggests some sort of error but I cannot find it.  

      I would expect a result of 3000, and for 66, a result of 2000 for this particular customer.

      The table I have this SQL on is joined to the customer table by the customerID, nothing else.

      Any help appreciated.

      Thanks

        • 1. Re: ExecSQL struggle
          Stu412

          OK, this is fixed - Value is among many others, a reserved word which isn't usable in SQL.  Changed the field name and it's fine now.

          • 2. Re: ExecSQL struggle
            philmodjunk

            Instead of renaming the field, you can enclose it in quotes:

            SELECT Sum ( \"value\")

            many of us simply enclose all table and field names in quotes like this rather than play trial and error games with the function to figure out which one needs quotes to eliminate the dreaded ? result.