Editing the SQL for Views

The Advanced (SQL) sub-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.

Returning a Random Set of Records

There are many business scenarios where an organization may find it extremely desirable to select a random set of records from a database. For example, an organization can use a random set of records to create a sample population for surveys, to reduce the size of a pool of prospects for a marketing campaign, or when analyzing data for statistical purposes.

The following example illustrates a common business use case that might require a random selection of data:

A marketing department decides that it wants to target 2,000 of its customers and prospects in a certain region of the world, perhaps North America.

A user creates a view in Aptify using demographic filters for North American countries. However, in this case, the view results contain over 7,000 records.

The marketing department wants to reduce the number of results to fit the sample size of 2,000 records. One way to accomplish this is to add additional filters to the view to target other customer attributes, such as business type.

However, in some cases, the marketing department may prefer to simply pick a subset of records from the base set. In this case, random selection is very useful.

Using Aptify, users can create views that return a defined number of records based on any combination of criteria, including the ability to return records randomly.

This section describes how to generate a random set of records based on a dynamically assigned Globally Unique Identifier (GUID).

The T-SQL NewID function generates a GUID value for each record using the MAC Address of the server's network card plus a unique number from the CPU clock. A GUID is a represented by 32 hexadecimal digits (0 - 9 and A - F) in the following format: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

These values are random, so this function can serve as the basis for randomly selecting records from a view's resultset. Refer to Microsoft's SQL Server Books Online for more information on the NewID function.

Follow these steps to create a list view that returns records randomly based on a GUID assigned by the NewID function:

1. Create a new list view for a service.
   • For example, right-click the Persons service and select Create View to open the dialog for a new Persons view.
   • This approach can be used to create random selections for any top-level service in Aptify.
2. Enter a Name for the view.
3. Select List View as the View Type.
   
4. If you want to filter the records based on one or more filter criteria, click the Filters tab.
   • For example, you could add a country filter, so the view only selects records randomly from those Persons in North American countries.
5. Click the Fields tab and specify the set of fields that should appear in the view. You can skip this step if you want to display the default set of fields.
6. Click the Paging tab and clear the Enable Paging option to disable paging.
7. Click the Advanced (SQL) tab.
8. Add the TOP keyword and the number of records to display after SELECT and before the list of fields, as shown below.
   
   • Example 1: To display 50 records, enter TOP 50.
   • Example 2: To display 10% of the total records, enter TOP 10 PERCENT.

   

9. At the end of the SQL statement, add an ORDER BY NewID() clause, as shown below.
   
10. Click OK to load the view.

• • The view displays a random set of records from the service. The number of records returned depends on the TOP keyword you specified.
    
    

The set of displayed records changes each time you refresh or reload the view. Therefore, in order to capture a particular set of random records for future use or for tracking purposes, add the results to a List after loading the view.

.