Table of contents
To use query views, the Domino server must be using DB2 as the back end, have the DB2 Access server installed and have the database DB2 enabled.
By using query views on the Web, you can access any data that is stored in the DB2 tables. By passing information to the view, you can build dynamic search queries. For web views, you can use @UrlQueryString("ParmName") to retrieve any parameter that is passed to the view. For Notes client access, you can use the @Environment function to pass parameters to the view. The queries can return fields that are not limited to the ones that the developer created in the view. Unlike normal Notes views that have the query and column values hard coded at design time, you can manipulate the query and return fields. This means that you can build one view that lets you pull the customer information, department information, or book descriptions. The only requirement is that a particular column can only return one data type. If the column returns a text string, then it cannot return a number with a different query, but it could if the number can be displayed as text.
Query views are designed so that you can only build an SQL statement that produces a result set. This is a security measure against inadvertent record deletion or change.
The query views don't show the view total for columns with total selected. Domino Date fields that have the default time component may show the date off by one day in the view.
Error messages are not very clear. If you get one, first check the fieldname in the SQL query. Then check the DB2 table access. These are the biggest source of errors.
The Query views excel in response time for certain types of queries. If you want to show all 100,000 documents, then a Notes view is faster because the index is already built. However, if you want specific documents, such as all documents created last quarter, then the Query view is significantly faster. Since on the Web, you normally only show a few rows (30 to 50) at a time. In this case, Query views do help.
The following table shows timing results. It shows Notes views versus query views to retrieve documents from a database with 100,000 documents and having a result set of about 1000 documents.
Date < 1/1/2007
Field = xxx
28 min - first opened
The Query view is also smaller since the view index is not stored. Remember that view options such as sortable columns, categories, multiple columns sorted, calculations and extending the column, all cause the view to be slower regardless of the type. See the View design elements
section for more information about views.
When you create query views, in the Create View window (in the following figure), you must select By SQL Query for Selection conditions. This is the only way to have a query view.
Create View window
When you create a query view, select By SQL Query
last. When you select a view to copy the design from, it overwrites the Selection conditions.
In Designer, the view has a different icon in front of it to indicate that it is a query view.
View list in Designer
Now when you open the view in Designer, you have an new object called SQL Query in the object list. The following figure shows where you build your query.
Query view in Designer
The tables that you normally access are created from DB2 Access Views (DAV). The DB2 Access Views table name is created by the server by using the database name that DB2 Access Views is in, along with the DB2 Access Views name. For example, the table CRM_SUP8.TICKETDAV_T is from the crm_support.nsf file where the DB2 Access View named TicketDAV resides. The server takes the first seven characters of the file name, adds a number (this is the schema name), and then the DB2 Access Views name. The table with the _T at the end is the data, and the table with _X is the index.
You can also access multiple tables and display the information in the same view. You are not limited to just data from DB2 Access Views. You can access data in any DB2 table. You can have a view that shows information that is combined from other databases. Using @dblookup is faster with the view in the current database than accessing another database. Therefore, put a query view in the current database to provide the information that is needed. You can also filter it so that only the information that is needed for the database is in the view, making it smaller and faster.
The db2 schema name is not guaranteed to be stay the same, if you use a template on a new server you may find the schema has a different name. So instead of having to change the view design each time you can use @functions to calculate it. @DB2Schema( @DbName ) will generate the schema name for the current database.
Also you must click the Create
buttons manually to access the table that is created from the DAV.
The following code allows a DB2 Query View to be composed dynamically, by passing the Department information as part of the URL.
URLParam := @UrlQueryString("Dept");
Clause := @If( URLParam ="" ; "" ; " AND DeptName='"+URLParam+"'");
T1 := @DB2Schema(@dbName) + "Dept_T";
T1 := @DB2Schema("student.nsf")+ ".students_T";
"SELECT D.DeptID , D.DeptName As DeptName , E.DeptID , E.Lastname AS Lastname FROM " +
T1 + " AS D, " + T2 + " AS E WHERE D.DeptID=E.DeptID" + Clause
You can get qu ite complex. The following code example lets a Web user search for support tickets by ticket number, c ompany name, status, or assigned name and have the results displayed in the view categorized by which ever field they want. You can now use one view instead of multiple views. The following example shows the sample SQL query:
CatField := @UrlQueryString("cat");
status := @UrlQueryString("stat");
ticket := @UrlQueryString("tkt");
user := @ReplaceSubstring(@UrlQueryString("user");"_";" ");
cmpy := @LowerCase(@ReplaceSubstring(@UrlQueryString("cmp");"_";" "));
company := @If(cmpy="";""; " AND LCASE(COMPANYNAME) LIKE '%" + cmpy + "%' ");
ufilter := @If(user="";""; " AND ASSIGNMENT LIKE '%" + user + "%' ");
filters := "Where STATUS_DESC='" + Status + "' " + company + ufilter;
stat := @If(ticket !=""; "Where TICKETID LIKE '%"+ticket+"%' "; filters);
cat := @If(CatField ="";" STATUS_DESC ";CatField)+ " as db2Cat, ";
"SELECT " + cat + " #NOTEID, #UNID, COMPANYNAME, CONTACTNAME, DESCRIPTION, MODIFIED_DATE, ASSIGNMENT, STATUS_DESC, TICKETID" +
" FROM " + @DB2Schema( @DbName ) +".TICKETDAV_T " + stat
The first column is categorized and uses the following column formula:
@If(db2cat ="";"* Not categorized";@Name([CN];db2cat));
With this example, the categorized column will be the fieldname that is passed as the &cat parameter in the URL. Therefore, you can have it categorized by contact, status, company, or by any field in the table.