4 Replies Latest reply on May 31, 2012 5:37 PM by rhlilienkamp

    ExecuteSQL error in Field Calc vs Data Viewer

    jamest

      I have an ExecuteSQL statement which works completely fine in the Data Viewer, and Developer Assistant, yet when I put the same expression into a field calculation I get the question mark. It's not an overly complicated expression, mearly trying to return the aggregate value of percentage values from related records.

       

      This is the expression:

      ExecuteSQL (

      "SELECT SUM(weight) FROM assessmentList AL

      JOIN assessmentGrade AG on AG.fk_assessmentID = AL.zz_kp_id

      JOIN Rclass R on AG.fk_RclassID = R.zz_kp_id

      WHERE R.zz_kp_id = ?

      "

      ; ""; ""; Rclass::zz_kp_id)

       

      I've tried simply removing the 'Rclass' from the arguments as the expression is being executed in a field called testfghs in the Rclass table.

       

      The TOs not shown in the graph are some SQL related tables and developer versions of each table in the database which is table name preceeded by '@'. I have tried running my expression from both contexts, though it shouldn't matter as I understood the ExecuteSQL() statement created relationships at time of execution.

       

      I get expected results across all records I have setup currently (5) with the data viewer.

       

      If I set the expression to:

      ExecuteSQL (

      "SELECT SUM(weight) FROM assessmentList AL

      JOIN assessmentGrade AG on AG.fk_assessmentID = AL.zz_kp_id

      "

      ; ""; ""; Rclass::zz_kp_id)

      I get results, though not what I am looking for. It seems to break with the second JOIN statement, though I cannot see why.

       

      Any help would be greatly appreciated, after staring at this for several hours and trying different processes I am unable to find the issue.