4 Replies Latest reply on Oct 5, 2012 7:04 AM by Greg

    ExecuteSQL Results Capture


      Good Day All,


      I have seen and read much on the new ExecuteSQL Function but I have a question concerning capturing / displaying the results of the Query.


      Some examples just seem to use a Text Field others appear to be much more complicated. I have seen examples that just say use a Varible or a Merge Varible.


      Where can I find instructions on using and or just displaying the results.


      I have a query that displays the results perfectly in the data viewer but no where else.



        • 1. Re: ExecuteSQL Results Capture

          Greg. The results will be TEXT regardless of being stored in field or merge field or shown in web viewer. The separators, also play a big part in how the text gets displayed. If you use the TAB as the separator in the query, the "tab-stops" can be set on a field to make these appear as "columns". But you could have the separators as anything (including html table tags!), these show better in a web viewer as "table".


          I should think a bigger question: what do you want to do with the result (other than display)? This is where some parsing would be great!


          Here is the information on tab-stops, if that's what you wanted:





          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL Results Capture



            Thanks as always...


            I like the idea of the "Tab-Stops" being set on a field.


            This particular Query is to find records that match the Where statements and then export them to Excel.



            • 3. Re: ExecuteSQL Results Capture

              Ah! I did something similar with ExecuteSQL and put the "header row" in, too:


              Let (
                  [ _col_delim = char(34) & "," & char (34) // this could be: "\",\""
                  ; _row_delim = char(13) // this is the carriage return
                  // SET UP THE HEADER ROW HERE
                  ; $query_h = "\"Last" & _col_delim & "First" & _col_delim & "State" 
                      & _col_delim & "Zip\"" & _row_delim
                  ; $query = "SELECT '\"'+lastname, firstname, state,''''+zip+'\"' 
                      FROM names WHERE lastname LIKE ? ORDER BY lastname, firstname"
                  ; $result = $query_h & ExecuteSQL ( $query ; _col_delim ; _row_delim; "Ab%" )
                  ]; "$result

              I get back something like this:



              What are you doing to "save" the results to a file for Excel, Greg?


              p.s. the extra single quote before the zip is my attempt to force Excel into keeping it as literal text, not a number.

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL Results Capture

                Thanks again,


                I have not reached the point where I am actually trying to save the results to Excel. My intent was to export the results to Excel.


                I like what you have shown me here so I am going to modify my calculation and try to match what you are doing in your example.


                I will let you know how it goes.


                This really is truly amazing!