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

    Using ExecuteSQL to Find Field Content

    RobertWard

      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)

       

      Loop

       

      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.