Hemi Help File
- Hemi Search Engine Introduction
- Query Tab
- Overview (describe initial display)
- Search Criteria row
- Query Selector
- Records Returned
- Design Tab
- Overview
- Assigning Columns to Display
- Changing Order of Columns Displayed
- Changing Sorting of Columns Displayed
- Library Tab
- Overview (describe data displayed)
- Saving Queries
- EditingQueries
- LoadingQueries
- Overwriting Queries
- Admin Tab
- Overview
- SQL Query Source
- ExportingData
- Advanced Topics
- Multi Row Queries
- Use of Parenthesis
- And/OrQueries Groupings
The hemi is a search engine that can be used to build queries and display data derived from those queries.
As shown above, there are four tabs associated with the hemi. A user will see three of these tabs; as the Admin tab is available only to sysadmins. The Query tab sets up the conditions for the query. The Design tab allows the user to choose the fields to output in the query and set up sorting. The Library tab is where queries can be saved for later use. The Admin tab contains a utility to examine the SQL code that defines the current query and utilities to export the current dataset to Excel or XML.
The Query tab
First, the Query tab is examined. In this example, one line has been defined for this query. Lines are selected by selecting the green plus sign icon and deleted by the red X icon. More on multi row queries under the advanced topics section.
The field is selected from the dropdown box as is the operator which defines how the query field is related to the criteria.
In this example, The LAST_NAME is being filtered by selecting records where the contents of this field start with ‘b’. Note that while this criterion were entered in “free form”, it is also possible that it could be entered from pick lists as shown below.
The field ‘GROUP ROLE’ requires selecting from a pick list. If using the ‘IN’ operator with this field, a pick list as shown below would be rendered to generate the criteria list.
It is also possible to load predefined queries from the library (more on this in the discussion of the library tab ).
After selecting the SEARCH button, the recordset is displayed.
This table explains the Operators offered above.
Operator: / Returns all records where the field value:= / equals the criteria
is greater than the criteria
>= / is greater than or equal to the criteria
Is less than the criteria
<= / Is less than or equal to the criteria
Is not equal to the criteria
Starts With / contains the criteria at the beginning
Ends With / contains the criteria at the end
Contains / contains the criteria anywhere
Like / Equals the criteria, with the use of wildcards ( % )
Not Like / Does not equal the criteria, with the use of wildcards ( % )
Between / Is between two values listed in the criteria
Not Between / Is not between two values listed in the criteria
In / Is contained in a list defined in the criteria
Not In / Is not contained in a list defined in the criteria
Exist / Has a value
Not Exist / Has no value
The Design tab
The fields to display and their order can be defined in the Design tab.
Selecting the EXCHANGE button applies these changes to the query.
Up to four levels of sorting can be defined.
Selecting CHANGE SORT will apply these sort levels to the query.
The Librarytab
Using the Library tab, it is possible to save queries and run these predefined queries at a later time.
In order to save a query that has been defined under the Query tab, select the green “plus sign” icon and give it a name and a description. Checking the PUBLIC? Field will make this query available to all users (this function is not available to everyone). With the ‘PUBLIC?’ Field unchecked, the query will be available to the user who defined the query only. Checking the STANDARD? Checkbox allows the defined query to be loaded directly while under the Query tab.
In order to save the defined query, select the yellow pencil icon.
To cancel adding the defined query, select the red X cross / arrow icon.
To edit the record for a predefined query, select the yellow pencil icon.
To delete a predefined query, select the red X icon.
Even if the ‘STANDARD?’ box is not checked, it is possible to load the predefined query and then run it under the Query tab as the active query.
It is also possible to overwrite the predefined query with the active query currently loaded under the Query tab.
The Admin Tab
The Admin tab contains the ability to view the SQL code for the current Query. It also provides the functionality to export the dataset of the current query to Excel or XML.
As shown below, selecting ‘QUERY SQL’, yields the SQL source code for the currently loaded query.
Selecting EXCEL allows for the exporting of the recordset for the currently loaded query to an excel spreadsheet. It is exported directly into an excel file as shown below. It is also possible to export this data to XML.
The Admin tab is not available to all users.
Advanced Topics
In order to perform effective queries, it is necessary to perform multirow queries. In this example, The LAST_NAME is being filtered by selecting records where the contents of this field start with ‘b’. The FIRST_NAME field is being filtered by selecting records where the contents of this field contain ‘ar’ or ‘am’. The resulting recordset comes from the criteria for both the LAST_NAME and FIRST_NAME being true.
The parenthesis is used so that operations are conducted in the proper order. In this example, the OR operation is applied before the AND operation.
Important is the use of the correct JOIN (i.e. AND/OR)An OR operation will return a record where any criteria is true. An AND operation will return a record when all criteria are true.
In multirow queries, the order of these AND / OR operations is vital to returning the correct recordset. While this order is implicitly defined in the absence of parenthesis by the order they are encountered, this is rarely the intended result. It is always best to explicitly define the order of operation by the use of parenthesis.
16 April 2008 General Dynamics – Information TechnologyPage 1 of 12