4 Replies Latest reply on Apr 1, 2015 4:59 PM by user19752

    ExecuteSQL syntax


      I have the following ExecuteSQL command I am trying to use but seem to having a syntax error as it is not calculating.


      ExecuteSQL ( "SELECT SUM (list_tot_sc)

                              FROM CTR_JOB

                              ORDER BY Date_Start DESC

                              FETCH FIRST 100 ROWS ONLY

                              WHERE zwf_CTRID = ? " ; "..." ; "" ; $CTRID   )


      SELECT SUM (list_tot_sc) is ~160 records

      FROM CTR_JOB is the location

      ORDER BY Date_Start is the sort field DESC I would like a descending sort order

      FETCH FIRST 100 ROWS ONLY I would only like to see the last 100 records

      WHERE zwf_CTRID = ? " ; "..." ; "" ; $CTRID only for this contractor


      The reason for the filtered view is that I was running the below script and it was functioning just fine. That said we added new Jobs to this contractor this week and the calculation is generating "?" now. Is there a threshold as to the number of records ExecuteSQL will consider? The below script is performing just fine on other contractors that have few jobs.


      ExecuteSQL ( "SELECT SUM (list_tot_sc)

                              FROM CTR_JOB

                              WHERE zwf_CTRID = ? " ; "..." ; "" ; $CTRID   )


      Any thoughts or insight will be greatly appreciated,


      Jason Farnsworth