sql: Write SQL text
In nwfsc-assess/PacFIN.Utilities: Generate fishery composition data from PacFIN data for the NWFSC

sql	R Documentation

Write SQL text

Description

Write SQL text as a single character string that will result in getting the relevant data from the PacFIN database.

Usage

sql_area()

sql_bds(pacfin_species_code)

sql_catch(pacfin_species_code, council_code = "P")

sql_species()

sql_check_FINAL_FISH_AGE_IN_YEARS()

Arguments

pacfin_species_code

A vector of strings specifying the PacFIN species code(s) you are interested in. This has sometimes been referred to as "SPID" in legacy sql scripts. An example for sablefish would be pacfin_species_code = "SABL". Lists of species codes in hierarchical order, by organization, and alphabetically organized can be found on the PacFIN website. Often you will want to include nominal species categories. Where, nominal (i.e., existing in name only) means information for a given species that is "derived" from non-species specific information, e.g., species complexes that are split out by species compositions like "nominal aurora rockfish" which would be ARR1. For some functions, these nominal categories can automatically be added, see the argument addnominal.

council_code

A vector of character strings specifying the council code that you wish to retain data for. The default "P" means data will only be returned for the Pacific Fisheries Management Council. Other accepted values are "N" or "*".

Details

sql_area() results in area data

sql_bds() results in biological data

sql_bds() results in catch data

sql_species() results in data frame of species names

sql_check_FINAL_FISH_AGE_IN_YEARS() results in a data frame of number of unique FISH_IDs present in PacFIN per PACFIN_SPECIES_CODE and AGENCY_CODE, i.e., species and state sampling agency, that have entries for AGE_IN_YEARS but have NULL values for FINAL_FISH_AGE_IN_YEARS. In theory, this table should have zero rows. But, there may be reasons why these entries do not have a final age value and potentially should not be used in assessments of population status. Thus, the result is used in an automated check and to create warning labels when downloading data.