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.
A reference to the Stonefield Query Application object and the SQL statement Stonefield Query created for the report (including joins to other tables).
The SQL statement to execute
This example unions the Customers and Suppliers tables into a single result set.
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
© Stonefield Software Inc., 2021 • Updated: 04/27/21
Comment or report problem with topic