The Advanced (SQL) tab shows the exact SQL syntax for List and Pivot Table views. The owner of the view can edit this SQL statement.
Advanced users and administrators can use this option to write their own SQL statement WHERE or ORDER BY clauses or to edit the SQL statement that is automatically generated by the system based on the standard configuration options.
Keep in mind the following when working with the Advanced SQL tab:
- You can specify your own WHERE and ORDER BY clauses on the Advanced (SQL) tab.
- You cannot modify the list of fields returned in the view from the Advanced (SQL) tab. Specify the fields you want to display on the Fields tab first, and then click the Advanced (SQL) tab to specify your own filters or sorting preferences.
- The viewing system does not support an alias for an entity's Base View. Other areas of the viewing system, such as sorting and paging, assume that an entity's base view is not aliased. However, you can alias and join other views that are database objects but not the base view into the query.
- Changes made to the SQL statement override the settings specified on the Filters and Sorting tabs. If a user later makes changes to the options on one of these tabs, a message box appears notifying the user that saving these changes will override the custom statement found under the Advanced SQL tab.
- When Paging is enabled, the system automatically appends a TOP N phrase (where N is the number of records to display per page) to the right of SELECT and an ORDER BY clause to the statement, even though they do not appear in the Advanced (SQL) tab. If you want to use the TOP keyword or an ORDER BY clause in your view definition, clear the Enable Paging option on the Paging tab before editing the text in the Advanced (SQL) tab.
- Note that the Advanced SQL option is only available for List and Pivot Table Views.
- Note that you can configure a view to display a random set of records through the Advanced (SQL) tab. See Returning a Random Set of Records for more information.
- Note that any sorting within an Advanced SQL-based list view is applicable only to the current view results and is not persisted to the view's properties.