3 Replies Latest reply on Jan 16, 2013 3:50 PM by RobertWard

    Using ExecuteSQL to Find Field Content


      I have a client file with some 84 fields. The client would like to have some means to report on the 84 fields which do contain data. I think ExecuteSQL is the best way to go about the problem, however I am an lite on implemention the ExecuteSQL function.


      To begin with, I have to use this in a script and loop through the script to evaluate each of the fields. The return number results go into a repeating field named EmptyValues. So, what I have is this.




      Set Var to $_field_list ; FieldNames (Get (FileName) ; Get (LayoutName) )

      Set Var $_rep to 1 (for EmptyValues field)

      Set Var $_counter to 1

      Set Var $_field_list_rep to 1 (number for the GetValue function)




      Set Field EmptyValues[$rep] ; ExecuteSQL ( Select Count ( GetValue ( $_field_list ; $_field_list_rep ) FROM Facility WHERE ( GetValue ( $_field_list ; $_field_list_rep ) NOT IS NULL" ; "" ; "" )


      Set Var $_rep to $_rep +1

      Set Var $_conter to $_counter +1

      Set Var $_field_list_ rep to $_field_list_ rep + 1


      End Loop




      the results I am getting back is ?. I am going wrong somewhere in the syntax. I am not sure if it's the variables, the WHERE clause or what.


      Appreciate any insight from any SQL Pros out there.