How to create and run a query using the Query Builder

From PTAGISWiki

Retrieving data from the PTAGIS database can be accomplished by several different methods, including pre-aggregated reports, interactive reports, and the Query Builder. The Query Builder allows you to specify which data to retrieve and how to sort and summarize the results. In order to use the Query Builder, you need to first create and then log in to an account on the PTAGIS Web Portal.

Contents 1 Start Query Builder 1.1 Datasource Selection 1.2 Field Selection 1.3 Filter Selection 1.4 Sorting 1.5 Summary Selection 1.6 Finish 2 Running the query

Start Query Builder

After logging in to the PTAGIS Web Portal, you can start the Query Builder by clicking the button on the Home menu. You may also click the My Queries link to the left and then click on the Query Builder button.

After Query Builder launches, it will step you through the creation of a query in six steps:

1. Datasource Selection
2. Field Selection
3. Filter Selection
4. Sorting
5. Summary Selection
6. Finish

Datasource Selection

The first step in building a query is to select the data source from the drop-down list for the type of PIT tag data which you would like to query.

Interrogation Detail 

Each Interrogation Detail record reports, by site, the detection of a single PIT tag at a single antenna during a single second. Each PIT-tagged fish is generally detected once per antenna as it passes through between four and 12 antennas at any of the juvenile fish bypass systems in the mainstem Snake and Columbia rivers. Adult fish, due to their tendency to wander up- and down-stream within a fish ladder, may be detected dozens, hundreds, or even thousands of times at a single site. Resident tagged fish at in-stream sites may also be detected numerous times over long intervals. The Interrogation Detail query is useful when analyzing the path taken by a specific tagged fish at a mainstem juvenile or adult fishway, or to analyze the behavior of individual tagged fish at in-stream interrogation sites. This data source should not be used to query multiple interrogation sites.

Interrogation Summary 

Each Interrogation Summary record contains the first and last date, time, and location of detections of a single PIT tag within a single interrogation site. If a tagged fish is detected at, say, three interrogation sites in the Columbia Basin, an Interrogation Summary query of all sites in the Basin will return three records for that tag code. The Interrogation Summary data source is commonly used to determine where and when a specific group of PIT-tagged fish are detected, or to retrieve a list of tagged fish that were detected at one or more specified sites during a certain date interval. At the juvenile fish transportation sites in the Snake and lower Columbia rivers, the last detection location will indicate if the fish was transported downstream or returned to the tailrace to migrate in-river.

Tagging Detail 

Provides access to tagging event records. Returns one record for each of the specified tag codes.

Recapture Details 

Provides access to recapture event records with corresponding tagging event data. Returns a record for each recapture event associated with each of the specified tag codes, providing there are tagging events for those tag codes.

Recapture Information 

Provides access to all recapture events. Returns a record for each recapture event associated with each of the specified tag codes, regardless of the existence of a corresponding tagging event.

Mort Details 

Provides access to all mortality events.

Field Selection

Select the fields for which to return data. The list of fields will change with the data source you select, though some fields are common to all data sources.

Click on the field names in the Available column that you wish to include in your query; hold down CTRL to highlight more than one at a time. Then click the > button to move them from the Available column to the Selected column. You may select all available fields by clicking the >> button. Clicking on the < or << buttons will move the selected field(s) or all fields back to the Available column, thus deselecting them from your query results.

Filter Selection

A filter is a restriction on a field that limits the number of records returned by the query. For instance, you can use filters to query by a date range, release site, interrogation site, or all three. You may set a filter on a field whether you have selected it for your query output in the previous step or not. When building a query with the Interrogation Summary or Interrogation Details data sources, careful consideration should be made in choosing filters. First Observation Date, Tag ID, Release Site, Release Date, HUC code and Migration Year are all excellent fields on which to filter.

• A field is available for filtering if the message "(no filter)" shows in the Restriction column.

• To filter a given field, click the Change button in the third column for the desired field name to open a new window in which to specify the filter criteria.

• If you select a Date field, you will be asked to input a start and end date and time.

• If you select a field that has a set of codified codes, such as Release Site or Organization, you will be able to select one or more of the codes from a list.

• Please see How to filter Tag ID and How to filter HUC Code, File ID, or River KM for details on filtering those fields.

• After you set the filter criteria, it will appear in the Restriction column next to the appropriate field name.

• Once you have selected all of the filters, click the Next button at the bottom of the page or the Sorting link to the left.

Sorting

You can specify one or more fields on which to sort the query results. You may sort fields in either ascending or descending order. You may sort on any field, whether you chose to include it in the query results or not. If you select multiple fields, the field closer to the top of the list will be sorted first, then the second field will be sorted, and so on.

Summary Selection

You can specify a field to summarize in your query; the most common summary is a count of occurrence. To use a count summary, include fields in your query that will provide criteria so that there is a group of fish to count. In other words, a Group By SQL statement will be executed on all the fields you have selected to be in your query, besides the one on which the count is being performed.

As an example, you can use the summary count to build a query that returns the number of PIT-tagged fish of each species and migration year to pass through an interrogation site during a specific time period:

To build a query to return results like this, use the following parameters in the query builder:

1. Data Source: Interrogation Summary
2. Fields: Species, Migration Year
3. Filters: Obs Site = Site(s) of your choice; Obs Date = After 1/1/2010 (or date range of your choice)
4. Sorting: Species = ASC; Migration Year = ASC
5. Summary: TagID = Count

Finish

Enter a Query Name (required) and a Description (optional) so that you can identify this query in the future. The report header, report footer and SQL query check boxes are not used and can be ignored. Click the Save button to save your query and close the query builder.

Running the query

After the query is defined and saved, it can be launched from the "My Queries" section of the site. Select the query by clicking the check box next to its name and then click the Run button.

Queries can take anywhere from a few seconds to a few minutes to run, depending on the fields selected as filters and the amount of records to be returned. The Status field will be updated to Completed once the query has finished and you can view your results by going to the My Report Files section. While a query is running, you are able to start a building a new query or editing an existing query. Queries can also be copied, renamed and deleted.