Although Stonefield Query automatically retrieves the data required for a query from a table, there may be times when you want to use a different SQL statement than a simple SELECT SomeFields FROM TableName. For example, a common use is to UNION two tables, such as current and archived invoices, into a single result set. The GetSQL script allows you to do that.

A GetSQL script is preferred to a Select script if possible. Select scripts cause a query to be split into individual tables when a report is run, which can make a report run slower, especially with tables with a lot of records. A GetSQL script can potentially be significantly faster since the query isn't split in that case but instead the returned SQL statement is used as a subquery for the main SQL statement for the report.

This script is specific for a table, so its actual name is table.GetSQL, where table is the name of the table. To create this script, click the Create "get SQL" script link when the table is selected in the TreeView.

You can't have both GetSQL and Select scripts for a table. Creating one disables the "create" link for the other.

Parameters
A reference to the Stonefield Query Application object and the SQL statement Stonefield Query created for the report (but just the portion pertaining to this table).

Return Value
The SQL statement to use as a subquery in place of the table name in the FROM or JOIN clauses.

Example
This example unions the Customers and Suppliers tables into a single result set.

Visual FoxPro

lparameters toApplication as SQApplication, tcSelect
local lcSQL
text to lcSQL noshow
select CustomerID as ID, 
      CompanyName, 
      ContactName, 
      ContactTitle, 
      Address, 
      City, 
      Region, 
      PostalCode, 
      Country, 
      Phone, 
      Fax,
      'Customer' as RecordType
   from Customers
union all
select CStr(SupplierID) as ID, 
      CompanyName, 
      ContactName, 
      ContactTitle, 
      Address, 
      City, 
      Region, 
      PostalCode, 
      Country, 
      Phone, 
      Fax,
      'Supplier' as RecordType
   from Suppliers
endtext
return lcSQL