Category
Lotusphere2008 BP106 QueryView
Bookmark :
The code for the QueryViews Tom Truitt and I demonstrated in the BP106 session at Lotusphere20008 is not in the
downloadable slides or in the
Analyzer application so I thought I'd write up something quickly here.
The first QueryView was simply a re-exposure of the Domino Access View (DAV) that was created from the data on Company forms. Normally, you'd just create a regular Notes view to show this data, but the idea was to do a Hello World like example for the first Query View. So, assuming you had a DAV out there for the Company form that you called Companies, you could construct a Query View with a SQL statement like this:
Schema := @DB2Schema("":"");
"SELECT COMPANYNAME, #NOTEID FROM " + SCHEMA + ".COMPANIES"
This first query does nothing more than ask Domino to tell us what the db2 schema is for the current database which we then use to construct a SQL statement for the CompanyName and NoteID for records stored in the Domino Access View (DAV) we'd originally created. SCHEMA.COMPANIES tells DB2 we're looking for a specific schema within the NSFDB2 database created for us by the setup of Domino for DB2 and within that schema, we want the Companies view which was created when we created the Companies DAV. All in all, it's not a very useful Query View as it could easily be more useful as a normal view, but it establishes that our QV process is working.
Schema := @DB2Schema("":"");
State := @Prompt([OkCancelList]; "Select State"; "Select the state to return companies for"; "TX"; state_list);
"SELECT COMPANYNAME, #NOTEID FROM " + SCHEMA + ".COMPANIES WHERE STATE = '" + State + "'"
This second Query View is a little more interesting in that it introduces the idea of soliciting end user selection of data before any query takes place. In this case, we're building a short list of states to select from. After the user selects one, we add it to the where clause of our SQL statement to bring back only records within the selected state.
state_list := "TX":"OK":"LA":"AR";
Schema := @DB2Schema("":"");
State := @Prompt([OkCancelList]; "Select State"; "Select the state to return companies for"; "TX"; state_list);
"SELECT COMPANY.COMPANYNAME, CONTACT.FIRSTNAME, CONTACT.LASTNAME, CONTACT.#NOTEID FROM " +
SCHEMA + ".COMPANIES AS COMPANY, " +
SCHEMA + ".CONTACTS AS CONTACT " +
"WHERE COMPANY.COMPANYNAME = CONTACT.COMPANYNAME AND " +
"COMPANY.STATE = '" + State + "'"
In this Query View we introduce the idea of a join by joining two DAVs to combine the data for a Company and the Contacts associated with that company. Where in the earlier code, the #NOTEID would have resulted in opening a document for the associated company, in this Query View, the #NOTEID is associated with the Contacts table and therefore opens the associated Contact record.
state_list := "TX":"OK":"LA":"AR";
Schema := @DB2Schema("":"");
State := @Prompt([OkCancelList]; "Select State"; "Select the state to return companies for"; "TX"; state_list);
"SELECT COMPANY.COMPANYNAME, INVOICE.INVOICEDATE, INVOICE.AMOUNT, COMPANY.#NOTEID FROM " +
SCHEMA + ".COMPANIES AS COMPANY, " +
"DB2ADMIN.INVOICES AS INVOICE " +
"WHERE COMPANY.COMPANYNAME = INVOICE.COMPANYNAME AND " +
"COMPANY.STATE = '" + State + "'"
In this Query View, we're branching out a bit. While before we were selecting from a single DAV or from 2 DAVs joined together, here we are joining a DAV to an external DB2 database table that contains invoicing information for our customers. The join will result in only showing company records where we have matching invoices for the selected state. This example introduces the idea of federated data structures. Given that the demo environment was all running on one machine, federation was only to data external to the NSFDB2 database (but still in the same DB2 server and instance). In the real world, that data could also be external and living in a separate DB2 server, like one that might be running on an iSeries...a point we ran out of time to make in the session.
Again, if you have any questions, don't hesitate to ask 
