Overview
Write queries in Structured Query Language (SQL) with the EQuIS SQL Form in EQuIS Professional to view and select data in the EQuIS database. The form does not allow INSERT, EDIT or DELETE statements. The EQuIS SQL Form must initially be downloaded from the EarthSoft Community Center and placed into the EQuIS installation directory prior to launching EQuIS Professional.
Exercise Scenario
We need to run several queries in the EQuIS SQL Form that will give us insight into both our specific facility and the EQuIS database as a whole. Then, we need to write and publish a custom query as a report that other EQuIS users may execute as needed.
|
Learn how to use the EQuIS SQL Form to facilitate data reporting and analysis. |
|
Exercise Objectives |
•Write Single-table Queries with the EQuIS SQL Form
•Write Multi-table Queries with the EQuIS SQL Form
•Write Queries to Explore the EQuIS Database with the EQuIS SQL Form
•Write (and Publish) Custom Reports with the EQuIS SQL Form
Skills, Software and Permissions Required
•EQuIS Professional 7 must be installed on the workstation
•.NET Framework 4.7.2 or higher must be installed on the workstation
•Admin permissions are required for the desired facilities
•Basic knowledge of SQL
•The EQuIS SQL Form (EarthSoft.Forms.Library.42804.dll) must be either already published to the EQuIS database or at least placed locally in the EQuIS installation directory (typically C:/Program Files/EarthSoft/EQuIS) for the Form to be visible
|
In the EQuIS Training Database, the EQuIS SQL Form has already been published and should already be visible. |
|
Objective: Write Single-table Queries |
These exercises use DT_FACILITY to demonstrate how to select all records and then narrow the query.
1.If necessary, reconnect to the EQuIS Training Database used in the previous exercise.
For this exercise, connect to the Gold King Mine facility in the EQuIS Training Database.
2.Select Forms from the Open group of the Home ribbon and open the EQuIS SQL Form. An EQuIS SQL Form tab will appear in the workspace.
3. Type this query into the main window of the form:
SELECT *
FROM dt_facility
4.Select the Open in New Tab 
option and then select Execute 
.
5.Review the results on the new Results tab. Note that results are returned for the entire EQuIS database and are not facility-specific.
6.Return to the EQuIS SQL Form tab and replace the query above with the following query:
SELECT *
FROM dt_facility
WHERE facility_id = @facility_id;
7.Select Execute .
8.A new SqlQueryReport tab opens and the query now has a parameter. Select Go to run the query.
9.Review the returned DT_FACILITY record. Note that results are returned for only the record that is facility-specific.
10.Return to the EQuIS SQL Form tab and replace the query above with the following query:
SELECT *
FROM dt_location
WHERE facility_id = @facility_id
AND sys_loc_code = @sys_loc_code;
11.Select Execute .
12.A new SqlQueryReport tab opens and the query now has selectable parameters. Select the desired Location.
13.Select Go to run the query.
14.Review the returned DT_LOCATION record.
|
Objective: Write Multi-table Queries |
1.Return to the EQuIS SQL Form tab. Delete the existing text and replace with this query:
SELECT l.facility_id, l.sys_loc_code, l.loc_name
, p.param_code, p.param_value, p.measurement_method, p.remark, p.ebatch
FROM dt_location l
INNER JOIN dt_location_parameter p ON l.facility_id = p.facility_id AND
l.sys_loc_code = p.sys_loc_code
WHERE l.facility_id = @facility_id
AND l.sys_loc_code = @sys_loc_code;
2.Select Execute .
3.A new SqlQueryReport tab opens and the query now has selectable parameters. Select the desired Location.
For this exercise, select SJCL-01.
4.Select Go to run the query. Note that results returned are facility-specific and include location and location parameter information for the selected location(s).
5.Return to the EQuIS SQL Form tab and replace the query above with the following query:
SELECT l.facility_id, l.sys_loc_code, l.loc_name
, p.param_code, p.param_value, p.measurement_method, p.remark, p.ebatch
FROM dt_location l
INNER JOIN dt_location_parameter p ON l.facility_id = p.facility_id AND
l.sys_loc_code = p.sys_loc_code
6.Select Execute .
7.Review the results on the Results tab. Note that results are returned for the entire EQuIS database and include facility, location and location parameter information.
|
Objective: Write Queries to Explore the EQuIS Database |
1.Return to the EQuIS SQL Form tab. Delete the existing text and replace with this query:
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_name LIKE 'dt%'
OR table_name LIKE 'at%'
ORDER BY table_name;
2.Deselect the Open in New Tab option and select Execute .
3.Review the results in Results pane. Note this query lists all data tables and associative tables in the EQuIS database.
4.Return to the EQuIS SQL Form tab and replace the query above with the following query:
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_name LIKE '%spm%'
ORDER BY table_name;
5.Select Execute .
6.Review the results in Results pane. Note this query lists all the tables related to the SPM Schema in the EQuIS database.
7.Return to the EQuIS SQL Form tab and replace the query above with the following query:
SELECT t.table_name, t.table_type
, c.column_name, c.data_type, c.CHARACTER_MAXIMUM_LENGTH, c.is_nullable
FROM information_schema.tables t
INNER JOIN information_schema.columns c ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE c.column_name LIKE '%CAS%'
ORDER BY t.table_name;
8.Select Execute .
9.Review the results in Results pane. Note this query lists all the tables that contain a column with "CAS" in them.
|
Objective: Write (and Publish) Custom Reports with the EQuIS SQL Form |
1.Return to the EQuIS SQL Form tab. Delete the existing text and replace with this query:
SELECT s.facility_id, s.sample_id, t.test_id, r.cas_rn
, s.sys_loc_code, s.medium_code, s.matrix_code
, t.analysis_date, t.test_type
, r.result_numeric, r.result_text, r.result_unit
FROM dt_sample s
INNER JOIN dt_test t ON s.facility_id = t.facility_id AND s.sample_id =
t.sample_id -- PK link of facility_id and sample_id between sample and test
INNER JOIN dt_result r ON t.facility_id = r.facility_id AND t.test_id =
r.test_id -- PK link of facility_id and test_id between test and result
WHERE s.facility_id = @facility_id
AND s.sys_loc_code IN
(
SELECT DISTINCT member_code
FROM equis.group_members(@group_code)
WHERE facility_id = @facility_id
)
AND s.matrix_code = @matrix_code
AND r.result_numeric BETWEEN @min_val AND @max_val;
2.Select Execute .
3.Note that although the Open in New Tab option was not selected, a separate tab opens because the query has selectable parameters. Enter the desired group_code, matrix_code, min_val and max_val parameters.
For this exercise, enter the following parameters:
Parameter |
Selection |
|---|---|
GROUP_CODE |
Utah |
MATRIX_CODE |
WS |
MIN_VAL |
10 |
MAX_VAL |
20 |
4.Select Go . Note that the query produces facility-specific records for the defined location group and matrix, and only between the defined minimum and maximum values.
5.Return to the EQuIS SQL Form tab and select to Save 
the query.
6.Browse to the desired file location, enter the desired name for the .sql file, and select the Save button.
7.Select Reports from the Publish group of the Home ribbon.
8.Browse to the location of the saved .sql.
9.Change the extension type in the bottom-right corner of the Open window to SQL Reports (sql).
10.Select the previously saved .sql and select Open.
11.Review the Report Publisher window and then select Save .
12.Select OK on the prompt that appears.
13.Select Reports from the Open group of the Home ribbon.
14.Select Refresh from the top-left of the window.
15.Note that the published .sql is now available to open and run as a report.
16.Select Go to run the query.