Suppose that you have a complex dataset that must be displayed in your web application. The application framework parses the command text in order to find expressions to correspond to data controller fields. These expressions are simple enough to used when dynamically constructing SQL SELECT statements at runtime.
However, the SQL parser in Code On Time has some limitations and may be unable to handle complex queries. In this situation, it would make more sense to offload the parsing to the database server and simply display the data as a view.
For example, suppose that it is necessary to display all phone numbers for shippers, suppliers, and customers grouped by CompanyName, Phone, and ContactName in a sample Northwind web app. Several UNIONs must be used in order to aggregate all the data. The SQL query is displayed below.
SELECT CompanyName, Phone, null as "ContactName"
FROM Shippers
UNION
SELECT CompanyName, Phone, ContactName
FROM Customers
UNION
SELECT CompanyName, Phone, ContactName
FROM Suppliers
GROUP BY CompanyName, Phone, ContactName
If one were to create a controller and use the query above as a command, they will receive an error message displayed below.
Instead, let’s save the query as a view and add the view to the project.
Creating the View
Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Views node, and press New View.
In the SQL Pane, paste in the SQL script displayed below.
SELECT CompanyName, Phone, null as "ContactName"
FROM Shippers
UNION
SELECT CompanyName, Phone, ContactName
FROM Customers
UNION
SELECT CompanyName, Phone, ContactName
FROM Suppliers
GROUP BY CompanyName, Phone, ContactName
Save the view with the name “PhoneNumbers”.
Adding View to Project
Start the web app generator. Click on the project name, and press Refresh. Check the box next to PhoneNumbers, and continue to refresh the project.
When refresh is complete, continue to regenerate the project.
Viewing the Results
When the web app opens in the default browser, navigate to New Pages | Phone Numbers page. The new view will be displayed without errors.
