9 Replies Latest reply on Oct 19, 2012 10:44 AM by monkeybreadsoftware

    ExecuteSQL differences - calc field vs Data Viewer

    TravisButler

      I've been struggling with a piece of ExecuteSQL code for the last couple of days now. When I try to debug it in Data Viewer, the SQL statement works fine; however, when I try to use it in a calculation field, it returns the dreaded "?". The obvious difference seems to be that the Data Viewer executes outside of a table context, while the calc field is in the table that's being queried; however, I've tried everything I can think of, or have been able to find that sounds like it might be relevant, and had no luck.

       

      I've tried to reduce the ExecuteSQL query to the absolute minimum and eliminate as many variables as possible, to try and narrow down the cause, as follows:

       

      ExecuteSQL( "SELECT Service_ID FROM Services WHERE Surface_ID = ? AND Service_Description = ? AND Season = ?"; ""; "";

      "4647"; "Plow Lot"; "11/12")

       

      In the Data Viewer, this works perfectly; it also worked with the original construction, using fields/variables instead of constants as the three parameters. However, when used as the definition of a calc field in the Services table, it fails every time.

       

      I've tried using aliases to Services, as some posts suggest is necessary for a self-join:

       

      ExecuteSQL( "SELECT s.Service_ID FROM Services s WHERE s.Surface_ID = ? AND s.Service_Description = ? AND s.Season = ?"; ""; "";

      "4647"; "Plow Lot"; "11/12")

       

      with no success. I've also tried changing the table context, using a different Services TO as the base for the calculation field; still no success.

       

      I'm guessing it's something simple I'm not doing right with the SQL syntax, but right now I can't think of what it is. Any suggestions?

       

      Many thanks in advance!