Database custom queries
A Custom Query allows a table to be defined as an SQL statement. It is usually preferable to create a View in the database and select the View from the Views tab. Designer is able to preview data from a database view but cannot preview data from a custom query.
A custom query should be a single SQL statement to retrieve a row set. If you want to perform more complicated processing such as a multi-line SQL script before running your row set query then this should be done as a pre-processing step.
<top> placeholder¶
The special string <top> is used by FastStats Designer to modify the custom query to improve performance when retrieving preview data.
The tag <top> will be substituted with the SQL command TOP 1 to improve performance when Designer is detecting the fields returned by the custom SQL query.
Example
If the custom query SELECT PersonURN, Surname, Forename FROM People then the database would execute the query before returning the results. By adding the tag <top>: SELECT <top> PersonURN, Surname, Forename FROM People then the query executed will be SELECT PersonURN, Surname, Forename FROM People when extracting the data or SELECT TOP 1 PersonURN, Surname, Forename FROM People when determining the fields used in the data source. For MySQL FastStats Designer will substitute a LIMIT clause and so <top> should appear at the end of the query.
The following values are substituted:
| Database | Substituted Value |
|---|---|
| Oracle | AND ROWNUM \<= 100 |
| MySql | LIMIT 100 |
| Any other (SQL Server, Access, etc) | TOP 100 |
Further examples¶
| Database | Example Custom Query |
|---|---|
| SQL Server | SELECT <top> PersonURN, Surname, Forename FROM People |
| MySql | SELECT PersonURN, Surname, Forename FROM People <top> |
| Oracle | SELECT PersonURN, Surname, Forename FROM People WHERE 1=1 <top> |
During extract the <top> placeholder is removed entirely to return the whole results set.