Using Database Views
| See also |
|---|
Overview
A database view assists with accessing data in the database, such that, multiple tables are joined together on specified keys and merged into a single view, and the view is then accessed as though it was a single table.
Database View of a Job
Many CCLAS EL applications, including Job, Data Entry, Analytical Report and Job Audit, require the user to select a job number. Job-based applications query the PROFJOB and PROFJOBUSER database tables.
Without using a database view, only columns from the PROFJOB table are available for display in the job list-view panel. Often the user requires that the data is presented in another format. This is where a database view is used to construct a logical PROFJOB table, and program settings are put in place so that all job-based queries then use the view instead of the database tables.
Using a database view allows for the following to be displayed in the job browser:
- Additional properties of the job (that is, those site-specific properties kept in the PROFJOBUSER table)
- Additional properties of other objects associated with the job (for example, displaying the client name instead of the client code, displaying the contact name instead of the contact code, or contact and address details)
- Aggregated statistics from any child table of the PROFJOB table, but grouped on the job number (for example, a dynamic count of the number of samples in a job, or a dynamic count of the samples not validated, or the number of tests in the job that belong to a particular laboratory section).
Note that the program continues to link to other tables in the CCLAS database, for example, CLIENT_CONTACT, PROFJOB_SCHEME, as required.
If the client tree-view style is used, for example in the Report application by setting the ListViewByClient REPORT program setting to enabled, then the PROFCLIENT_CLIENT and PROFCLIENT_PROJECT tables are also joined to the defined view.
The columns displayed in the job browser in the application are defined by the ListViewColumnn and SubnListViewColumnn application program settings for the staff member.
Note that, since the STAFFPROGRAMEDIT table defines the tables and views that are available from which to select the fields, then these settings are not specific to the laboratory or staff code used. Therefore, if there are different views used for different staff or laboratory codes, the user needs to take extra care when selecting the fields for the browser column headings.
The first step to setting up a database view to return a list of jobs is to create the view.
Example—Database view for the PROFJOB table
The next step is to configure staff settings. For the relevant set of staff settings, set the BrowserView setting to contain the name of the view. For example, for the Report application, set the BrowserView REPORT program setting to PROFILE_PJVIEW.
Set up the columns for display in the job browser by defining the ListViewColumnn and SubnListViewColumnn application program settings for the staff member.
Example—Defining the columns on display in the Report application grid
The following views are included in a default CCLAS installation:
