In Part 1, we examined how to use the SELECT statement in SQL to create a SQL View that can be used in Crystal Reports for reporting. There are two additional uses for the SELECT statement, which make learning its syntax an invaluable tool for database administrators.

Let’s start by creating another SQL View.

CREATE VIEW vMyObjectGeography AS

SELECT O.ObjectID AS ID
,O.ObjectNumber
,O.SortNumber
,T.Title
,O.ObjectName
,G.Country
,G.State
,G.City
,G.Longitude
,G.Latitude
,CONVERT(NVARCHAR(255),G.Notes) AS Notes
FROM Objects O
LEFT JOIN ObjTitles T ON O.ObjectID = T.ObjectID AND T.DisplayOrder = 1
LEFT JOIN ObjGeography G ON O.ObjectID = G.ObjectID AND G.PrimaryDisplay = 1

This query will return Object and Geography data. A few things to take notice of: we aliased the primary key as ID. We are also setting a limit to text fields, by converting them to NVARCHAR with a length of 255 characters. And we are limiting the joins from returning more than one response, which will help to prevent errors in TMS.

After you execute this statement and create your view, you will need to grant permissions to the TMSUser group, as demonstrated in the previous tutorial.

List View Designer

Open the List View Designer and login to your database. Select the “New” icon while focused on the Objects node of the List Views tab. Give your new List View a name, and select the SQL view you just created from the drop down.

Adjust the column widths to your liking. The Designer identified that ObjectNumber is to be sorted by the SortNumber; it applied “Sort by next” to the former, and “Hide” to the latter. However, Designer thinks that the Title is really “Title Sort” and that State is “State/Proof”. Check that the tables, columns and column headings accurately reflect the data source, and adjust accordingly. Save, activate, and include the appropriate security groups, and you now have a new List View in TMS!

When creating SQL Views for the purpose of making List Views we need to alias the primary key, for whichever module we want to include the List View in, as ID; for example, in Constituents the view would start ConstituentID AS ID. The rest is just a run of the mill, SELECT statement.