Overview	Top

Purpose

LabVantage uses queries for three purposes. The primary purpose is to filter a list or lookup page. Users can choose a query and apply this filter making it easier to find the data they need on a list. A second purpose is to determine which data is visible on a Dashboard. Administrators will design Gizmos. Many of these Gizmos support the use queries depending on the Gizmo Type. Lastly, the LabVantage Actions can execute a query. The Query List Page allows you to add or edit standard LabVantage Queries. These execute SDC-centric SQL statements.

Navigate to System Admin > Configuration > Queries

All standard queries that perform any relative 'date where' expressions use a substituted date-expression that is inserted into the query and evaluated by the RSET query routines. Examples include after a date, between dates, and others. These date expressions handle

• Honoring timezone adjustments
• Leveraging Date Indexes if and when they are defined.
• Provide consistency when it comes to Inclusive and Exclusive Date boundaries.
• Eliminates the need for expressions that determine your database platform and adjust the where clause accordingly.
• Provides equivalent date processing functionality between Oracle and MSSQL.

 Please see Query Date Handling for more detail.

Description	Top

Fields of the Query form:

This element lets you resolve all Arguments you have made in the WHERE clause, as well as specify how you choose the Argument resolutions when displayed.  For example, you can make a Query based on the User SDC, where sysuserid = [user]. Here, [user] is the single Argument. When you execute the Query, it first asks you to enter a value for the Argument. The Argument Definition lets you specify how the value of the Argument is entered.  Click the up and down arrows to sort Arguments in the desired order.

Fields of the Query Arguments detail:

In the Query Maintenance page, you can specify up to twelve arguments (also referred to in LabVantage as "parameters").

When using these LabVantage Java Public API methods in sapphire.accessor, you also specify up to twelve arguments:

Multiple arguments can be displayed in an SDI List page Search Bar (rendered by the advancedsearch Element).

The following example shows how you can use Cascaded Arguments, which use the resolution of one Argument to resolve another Argument.  All Queries and Arguments must follow SQL syntax rules of your DBMS.  The following examples use Oracle syntax.

In a List Page, list all SDIs that have been given a qualitative condition (Good, Bad, or Ugly).  Add the capability to list only the SDIs that are applicable to a specific Project that is assigned to a specific Project Manager.  In this case, the SDIs are special laboratory samples in a User SDC called NewSample.

This example builds the data structure shown below.

NewSample SDC
SDI	SampleCondition	Project		Project SDC
Sample-001	Good	Project_A		SDI	ProjectManager		User SDC
Sample-002	Good	Project_B		Project_A	User_1		SDI
Sample-003	Bad	Project_C		Project_B	User_2		User_1
							User_2

Add the NewSample SDC, then Add Columns and SDIs

We will call our example Query GoodSamples because we want to list u_newsample SDIs that have been classified as "Good". The GoodSamples Query:

SELECT * FROM u_newsample WHERE project = '[proj]' AND samplecondition = '[cond]'

This Query retrieves, from the NewSample SDC, all SDIs relevant to a Project and a Sample Condition.  Note that you must specify the actual table name for the SDC, u_newsample, which is prefaced by the u_ denoting a User SDC.

To resolve the arguments in the WHERE clause (Project [proj] and Sample Condition [cond]), you must define each.  The example below shows how each Argument resolves the Query.

Note that the Project [proj] argument can be resolved only after definition of a Project Manager [pm].  In other words, the Project Manager is required input for resolution ot the Project argument.  You must always define arguments as they will be executed by the Query.  The schematic above shows this "bottom-up" approach, analogous to "nested" instructions that must be carried out in sequence.  The table below shows the order in which you would define the Arguments in the Query Maintenance Page.

The illustration below shows how the Query behaves when executed in the u_newsamples List Page.  Using the List Page Properties, the Filter Bar is configured to contain Queries.  Clicking the Good Samples Query opens drop lists in a split-frame, allowing you to successively resolve each argument as shown.

When you specify calendar dates in Queries made to an Oracle database, you must use the to_date function.  For example, to write a Query that retrieves all SDIs created between a Start Date and an End Date, use the following syntax in the WHERE clause of the SQL statement:

WHERE createdt > to_date('[%adjstartdate%]', 'mm/dd/yy hh24:mi:ss' ) and createdt < to_date('[%adjenddate%]', 'mm/dd/yy hh24:mi:ss' )

For [startdate] and [enddate], set the Type (Argument Definition) to AbsrelDt.

WHERE createby='[%currentuser%]'

This Query retrieves all Samples created by the current User.

When "Union Query" is checked on the Query Form and the page is saved, the "Additional Union Clauses" detail tab is rendered to manage Queries that include a union between two separate from and where clauses. The primary query is the first query in the union. The "From Clause" and "Where Clause" in this detail are added to create the overall union query.

This is an alternative to using an OR in the where clause, as the performance of a UNION is superior to that of an OR. This is particularly effective in queries requiring different additional criteria based on the value of a given column. For example:

The examples below show two OOB Queries from LabVantage 8.2.x that have modified using unions for LabVantage 8.3.0: