AnsweredAssumed Answered

ExecuteSQL Function Works in Data Viewer, Not in Calculation?

Question asked by dchretien on Oct 5, 2017
Latest reply on Aug 27, 2018 by fishtech



I have a calculation that uses the ExecuteSQL function:


ExecuteSQL ( "SELECT COUNT(ControlNumber)

     FROM ComplaintItems

     LEFT OUTER JOIN Manufacturer ON ComplaintItems.SpplCde = Manufacturer.ManufacturerCode

     WHERE ComplaintQuarter_aec = 4

          AND ComplaintYear_aec = Manufacturer.link_ThisYear_aecg

          AND BrandedOrPrivateLabel_aec = 'P'

          and flag_void_aec <> 1

          AND flag_International_aec <> 1

          and SpplCde = ?" ;

     "" ; "" ; Manufacturer::ManufacturerCode )


In the Data Viewer, everything works correctly, and I obtain the values I expect when I'm in the "Manufacturer" table and switch from one record to another.


When I use this same calculation in a calculated field in the Manufacturer table, I receive "?" as the result.  (I did set the calculation to return Text, since that's what the ExecuteSQL function returns, even though the value is numerical, but that made no difference.


I just noticed that all of my "ANDs" are not upper-case, but it works in the data viewer.  I will fix that to see if that makes a difference in the calculated field.


Running version 16 on client and server.


Does anyone see what I'm missing?