Using ExecuteSQL to Find Field Content

Discussion created by RobertWard on Nov 15, 2012
Latest reply on Jan 16, 2013 by 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)




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.