Skip to contents

Common Query Examples

Daniel J. Hocking

2026-04-01

Source: vignettes/articles/common_query_examples.Rmd
common_query_examples.Rmd

Common SQL Query Examples

Below are examples of queries that demonstrate how to query and join primary CAMS end-user tables to produce commonly desired outputs.

The primary landings data are found in CAMS_LAND and the associated information about the trip and subtrip is found in CAMS_SUBTRIP. Additional information about the species is found in CFG_ITIS. Below is an example of a query to get haddock and Atlantic cod federal landings by year for 2018 - 2022 along with the number of trips and permits.

select
      year
      , l.itis_tsn
      , i.itis_name
      , count(distinct(camsid)) n_trip
      , count(distinct(permit)) n_permit
      , sum(livlb) total_pounds
from
      cams_garfo.cams_land l
left join
      cams_garfo.cfg_itis i
      on l.itis_tsn = i.itis_tsn
where
      l.itis_tsn in ('164712', '164744')
      and permit_state_fed = 'FEDERAL'
      and year between 2018 and 2022
group by
      year
      , l.itis_tsn
      , i.itis_name
  • Get subtrip info associated with landings or landings by subtrip

Joining in additional subtrip attributes

select
      l.itis_tsn
      , i.itis_name
      , s.em
      , count(distinct(l.camsid)) n_trip
      , count(distinct(l.permit)) n_permit
      , sum(livlb) total_pounds
from
      cams_garfo.cams_land l -- nefsc no longer uses cams_garfo schema prefix
left join
    cams_garfo.cams_subtrip s
    on l.camsid = s.camsid -- keep this separate from subtrip to use indexing
    and l.subtrip = s.subtrip
left join
      cams_garfo.cfg_itis i -- nefsc no longer uses cams_garfo schema prefix
      on l.itis_tsn = i.itis_tsn
where
      l.itis_tsn in ('164712', '164744')
      and l.year between 2018 and 2023
group by
      l.itis_tsn
      , i.itis_name
      , s.em

VTR orphan trips are those trips that do not match to a dealer trip but are expected to have a matching dealer trip. Trips without landed pounds (e.g. mechanical breakdown, no catch, set-only) and those that are only bait and/or home consumption are not considered orphans are are therefore kept in the CAMS_LAND and CAMS_SUBTRIP tables. Dealer orphan trips are kept in the officially CAMS landings (CAMS_LAND) so the VTR orphan trips are separated out to avoid duplication of the orphans due to data mismatch that result from data errors (e.g. incorrect permit number, incorrect docid/vtr number on the dealer report, etc.). The most recent (~2 weeks) VTR orphan trips are often temporary until the corresponding trips from other systems are entered and after that likely represent data errors or missing dealer records. These VTR orphans can be used as preliminary data and for quality assurance and compliance purposes.

select
      year
      , l.itis_tsn
      , i.itis_name
      , count(distinct(camsid)) n_trip
      , count(distinct(permit)) n_permit
      , sum(livlb) total_pounds
from
      cams_garfo.cams_vtr_orphans l -- nefsc no longer uses cams_garfo schema prefix
left join
      cams_garfo.cfg_itis i -- nefsc no longer uses cams_garfo schema prefix
      on l.itis_tsn = i.itis_tsn
where
      l.itis_tsn in ('164712', '164744')
      and permit_state_fed = 'FEDERAL'
      and year between 2018 and 2022
group by
      year
      , l.itis_tsn
      , i.itis_name

Joining in additional subtrip attributes

select
      l.itis_tsn
      , i.itis_name
      , s.permit_class
      , count(distinct(l.camsid)) n_trip
      , count(distinct(l.permit)) n_permit
      , sum(livlb) total_pounds
from
      cams_garfo.cams_vtr_orphans l -- nefsc no longer uses cams_garfo schema prefix
left join
    cams_garfo.cams_vtr_orphans_subtrip s
    on l.camsid = s.camsid -- keep this separate from subtrip to use indexing
    and l.subtrip = s.subtrip
left join
      cams_garfo.cfg_itis i -- nefsc no longer uses cams_garfo schema prefix
      on l.itis_tsn = i.itis_tsn
where
      l.itis_tsn in ('098678', '097314')
      and l.year between 2018 and 2022
group by
      l.itis_tsn
      , i.itis_name
      , s.permit_class

Getting the vessel name and info associated with the trip

-- total landings reported by permit-vessel for 1 year
select
    l.permit
    , v.ves_name 
    , v.ves_gtons
    , v.ves_tons
    , v.ves_len
    , sum(l.lndlb) lndlb_total
    , sum(l.livlb) livlb_total
from (
    select year, camsid, permit, lndlb, livlb from cams_garfo.cams_land
    union all
    select year, camsid, permit, lndlb, livlb from cams_garfo.cams_vtr_orphans
    ) l
left join
    cams_garfo.match_vpsvessel v
    on l.camsid = v.camsid
where
    l.year = 2024
    and l.permit <> '000000'
group by
    l.permit
    , v.ves_name 
    , v.ves_gtons
    , v.ves_tons
    , v.ves_len

not MRIP

Vessels on Party, Charter, or Recreational (commercial vessel hired out for recreation) trips indicate these during their trip report submission (VTR). These are usually distinct from commercial trips in terms of reporting details, permitting, and other expected data for the trip (e.g. no dealer report is expected). As such, the CAMS system separates these from commercial landings.

Commercial landings are in the CAMS_SUBTRIP and associated CAMS_SUBTRIP tables. Identically structured tables for recreational trips are CAMS_VTR_REC and CAMS_VTR_REC_SUBTRIP. IMPORTANT DETAIL: if a trip is designated as party/charter/recreational on the VTR they can still sell catch from the trip and those sold landings would go into CAMS_LAND while any unsold landings would go into CAMS_VTR_REC. This is most common on a for-hire trip where large tuna are reserved for sale but passengers get to keep other catch; however, this can happen with any species. In these cases the subtrip-level information can be in both CAMS_SUBTRIP and CAMS_VTR_REC_SUBTRIP but the individual landings will only be in one table.

Example 1: How many party/charter/recreational trips landed unsold flounder of each species in 2024?

select
    r.year
    , r.itis_tsn
    , i.itis_name
    , count(distinct camsid) n_trip
from
    cams_garfo.cams_vtr_rec r
inner join
    cams_garfo.cfg_itis i
    on r.itis_tsn = i.itis_tsn
where
    i.itis_name like '%FLOUNDER%'
    and r.year = 2024
group by
    r.year
    , r.itis_tsn
    , i.itis_name
order by
    itis_name

Example 2: How many recreational trips where there in each of the past three years including those that sold all of their landings?

select
    s.year
    , to_char(count(distinct camsid),  '99G999G999G999', 'NLS_NUMERIC_CHARACTERS=",."') n_trip
from
    (
    select distinct year, camsid from cams_garfo.cams_vtr_rec_subtrip
    union all
    select distinct year, camsid from cams_garfo.cams_land where rec = 1
    ) s
where
    s.year between 
        extract(year from sysdate) - 3 and
        extract(year from sysdate) - 1
group by
    s.year
order by
    year desc

Example 3: What were the five highest revenue species sold from party/charter/recreational trips over the past five year?

with totals as (
    select
        itis_tsn
        , sum(value) value
    from
        cams_garfo.cams_land l
    where
        l.rec = 1
        and l.year between 
            extract(year from sysdate) - 5 and
            extract(year from sysdate) - 1
    group by
        itis_tsn
)
select
    t.value_rank
    , t.itis_tsn
    , i.itis_name
    , t.value
from
    (
    select
        itis_tsn
        , value
        , rank() over(order by value desc) as value_rank
    from
        totals
    ) t
inner join
    cams_garfo.cfg_itis i
    on t.itis_tsn = i.itis_tsn
where
    t.value_rank <= 5
order by
    value_rank

CAMS calculates and reports discards for each species on all subtrips to allow analysts to aggregate discards as necessary. For example, analysts at GARFO often need discard totals by fishing year or even trimester for quota monitoring, whereas analysts at NEFSC often need discards aggregated to the calendar year for stock assessments. Uncertainty is also important to consider with any estimated values such as discards. CAMS reports the variance associated with each estimate, which properly accounts for covariance among subtrips within a stratification. This allows analysts to sum the variances along with the discards to get the total uncertainty associated with any aggregation.

Below are examples of aggregating discards for longfin squid by year in both pounds and metric tons along with metrics of uncertainty.

select
    year
    , round(sum(cams_discard), 0) discard_lbs
    , round(power(sum(cams_discard_variance), 0.5), 0) se
    , round(power(sum(cams_discard_variance), 0.5) / sum(cams_discard), 2) cv
    , round((sum(cams_discard) - (1.645 * power(sum(cams_discard_variance), 0.5))), 0) ci10
    , round((sum(cams_discard) + (1.645 * power(sum(cams_discard_variance), 0.5))), 0) ci90
    , round(sum(cams_discard) / 2204.6226, 0) mt
    , round(power(sum(cams_discard_variance), 0.5) / 2204.6226, 0) mt_se
    , round(power(sum(cams_discard_variance), 0.5) / sum(cams_discard), 2) mt_cv
    , round((sum(cams_discard) - (1.645 * power(sum(cams_discard_variance), 0.5))) / 2204.6226, 0) mt_ci10
    , round((sum(cams_discard) + (1.645 * power(sum(cams_discard_variance), 0.5))) / 2204.6226, 0) mt_ci90
from
    cams_garfo.cams_discard_all_years d
where
    d.itis_tsn = '082372'
    and year < 2023
group by
    year
order by
    year desc

Another simple example:

select 
   d.year
   , d.itis_tsn
   , s.negear
   , g.negear_name
   , round(sum(d.cams_discard)*0.000453592370, 1) as discard_MT
   , case when sum(cams_discard) = 0 then 0.0
       else round(power(sum(cams_discard_variance), 0.5) / sum(cams_discard), 3) end as discard_cv
from 
    cams_discard_all_years d
left join
    cams_subtrip s
    on d.camsid = s.camsid
    and d.subtrip = s.subtrip
left join
    cfg_negear g  -- reminder object is cams_cfg_negear on NEFSC DB
    on s.negear = g.negear
where
    d.year = 2024
    and d.itis_tsn = 164712 -- cod
group by 
    d.year
    , d.itis_tsn
    , s.negear
    , g.negear_name
order by 
    d.year
    , s.negear

Below is an example of getting the total catch for cod stock by stock area in 2019.

with landings as (
    select
      l.year
      , l.itis_tsn
      , a.assessment_stock_area
      , sum(livlb) landed_livlb
    from
      cams_garfo.cams_land l
    left join
      cams_garfo.cfg_stock_assessment_areas a
      on l.itis_tsn = a.itis_tsn
      and l.area = a.area
      and l.date_trip >= a.date_start 
      and l.date_trip < coalesce(a.date_end, sysdate + 1)
    where
      l.itis_tsn = '164712'
      and l.year = 2019     
    group by
      l.year
      , l.itis_tsn
      , a.assessment_stock_area
  ), discards as (
      select
        s.year
        , d.itis_tsn
        , a.assessment_stock_area
        , round(sum(cams_discard), 0) discard_livlb
      from
        cams_garfo.cams_discard_all_years d
    inner join
        cams_garfo.cams_subtrip s
        on d.camsid = s.camsid
        and d.subtrip = s.subtrip
      left join
        cams_garfo.cfg_stock_assessment_areas a
        on d.itis_tsn = a.itis_tsn
        and s.area = a.area
        and s.date_trip >= a.date_start 
        and s.date_trip < coalesce(a.date_end, sysdate + 1)
    where
        d.itis_tsn = '164712'
        and s.year = 2019
      group by
        s.year
      , d.itis_tsn
      , a.assessment_stock_area
  )
  select
      coalesce(l.year, d.year) year
      , coalesce(l.itis_tsn, d.itis_tsn) itis_tsn
      , i.itis_name
      , coalesce(l.assessment_stock_area, d.assessment_stock_area) assessment_stock_area
      , landed_livlb
      , discard_livlb
  from
      landings l
  full join
      discards d
      on l.year = d.year
      and l.itis_tsn = d.itis_tsn
      and l.assessment_stock_area = d.assessment_stock_area
  inner join
      cams_garfo.cfg_itis i
      on coalesce(l.itis_tsn, d.itis_tsn) = i.itis_tsn
    where
        coalesce(l.assessment_stock_area, d.assessment_stock_area) is not null

coming soon

with landing as (
    select
        l.camsid
        , l.subtrip
        , l.itis_tsn
        , l.negear
        , n.negear_name
        , n.secgear_mapped
        , sum(l.lndlb) lndlb
        , sum(l.value) value
    from 
        cams_garfo.cams_landings l
    left join 
        cams_garfo.cfg_negear n 
        on l.negear = n.negear
    where
        l.year = 2025 and
        l.itis_tsn = '164712'
    group by
        l.camsid
        , l.subtrip
        , l.itis_tsn
        , l.negear
        , n.negear_name
        , n.secgear_mapped
),
discards as (
    select 
        d.camsid
        , d.subtrip
        , d.itis_tsn
        , d.cams_discard
    from 
        cams_garfo.cams_discard_all_years d
    where 
        d.year = 2025
) 
select 
    b.camsid
    , b.subtrip
    , b.itis_tsn
    , b.negear
    , b.negear_name
    , b.secgear_mapped
    , g.cams_gear_group
    , b.lndlb
    , b.value
    , c.cams_discard
from 
    landing b
left join 
    discards c  
    on b.camsid = c.camsid 
    and b.subtrip = c.subtrip
    and b.itis_tsn = c.itis_tsn
left join 
    cams_garfo.cfg_gearcode_strata g
    on coalesce(c.itis_tsn, b.itis_tsn) = g.itis_tsn
    and b.secgear_mapped = g.secgear_mapped 
order by negear

Below is an example of code to get the cams version associated with each year of landings. All landings-related tables are run together for a full year at a time so have a single run date and version associated with that year.

select
    l.year
    , l.date_run
    , v.date_start
    , v.date_end
    , v.cams_version
from
    cams_landings_metadata l
left join
    (select cams_version, date_start, lead(date_start) over(order by date_start) date_end from cfg_version) v
    on l.date_run > v.date_start
    and l.date_run <= nvl(v.date_end, (SELECT systimestamp AT TIME ZONE 'GMT'
  FROM dual))

Discards can be run separately by fishing year and species, although often all managed species for a fishing year run at the same time, so they can be associated with different versions in theory. The query below is an example of getting the version of CAMS associated with each fishing year for a particular species.

select distinct
    d.year
    , fy
    , d.date_run
    , v.date_start
    , v.date_end
    , v.cams_version -- just trying to add this to any query with a date_run field

from
    cams_garfo.cams_discard_all_years d
left join
    (select cams_version, date_start, lead(date_start) over(order by date_start) date_end from cams_garfo.cfg_version) v
    on d.date_run > v.date_start
    and d.date_run <= nvl(v.date_end, (SELECT systimestamp AT TIME ZONE 'GMT'
  FROM dual))
where
    d.itis_tsn = '172933'

We hope to make convenience functions for these in the future.

CAMS does not attempt to carry through all upstream, base data from sources but rather just the most commonly used data. To connect the CAMS data tables to upstream data, we include the links and foreign keys to the other data sources (e.g. docid to VTR trips, vtr_imgid to VTR subtrips, link1 to observer trips, link3 to observer hauls, etc). Below is an example of joining in specific gear and subtrip attributes from the base data into the CAMS landing data.

-- Get landings at subtrip level
with landings as (
  select
    camsid
    , subtrip
    , sum(lndlb) lndlb
    , sum(livlb) livlb
  from
    cams_garfo.cams_land
  where
    year = 2024
  group by
    camsid
    , subtrip
)
select
  s.camsid
  , s.subtrip
  , l.lndlb
  , l.livlb
  , s.docid     -- link to VTR trip-level info (noaa.document)
  , s.vtr_imgid -- link to VTR subtrip-level info (noaa.images)
  , s.df  -- days fished
  , i.gearqty
  , i.gearsize
  , i.ntows
  , i.towhrs
  , i.towmin
from
  cams_garfo.cams_subtrip s
left join
  landings l
  on s.camsid = l.camsid
  and s.subtrip = l.subtrip
left join
  noaa.images i -- subtrip level info with gear details
  on s.vtr_imgid = i.imgid
where
  s.year = 2024

The value field in CAMS_LAND comes from CFDERS_ALL_YEARS and represents the ex-vessel value paid at the time of the sale. For economic comparisons over time, CAMS offers the CAMS_GDP_DEFLATOR support table with data from https://fred.stlouisfed.org/. Below is an example of converting ex-vessel values to 2022-adjusted values and calculating average annual price. This is a simple example, ignoring market and grade information along with month, port, region, or other economically relevant factors. This example is intended to give uses a basic example of how the table can be used, which then can be extended for more nuanced analyses. Similarly, any year or quarter of the year can be used as the standard for conversion.

-- standardize ex-vessel values to mean 2022 values
with values_2022 as (
    select
        l.camsid
        , l.permit
        , l.dlrid
        , l.year
        , l.itis_tsn
        , l.lndlb
        , l.value
        , d.deflator
        , l.value / d.deflator * 
        (select avg(deflator) from cams_gdp_deflator where year = 2022) value_2022
    from
        cams_garfo.cams_land l
    join
        cams_garfo.cams_gdp_deflator d
        on l.date_trip >= d.date_start
        and l.date_trip < coalesce(d.date_end, sysdate) -- must use less than to avoid cartesian join
    where
        l.itis_tsn in ('080944', '081343')
        and l.value is not null -- avoid missing values for price analysis
        and l.lndlb > 0  -- avoid missing pounds
        and permit_state_fed = 'FEDERAL' 
)
, mainq as (
select
    v.year
    , v.itis_tsn
    , i.itis_name
    , count(distinct camsid) n_trip
    , count(distinct permit) n_permit
    , count(distinct dlrid) n_dealer
    , round(sum(value) / sum(lndlb), 2) price_exvessel
    , round(sum(value_2022) / sum(lndlb), 2) price_2022
from
    values_2022 v
left join
    cams_garfo.cfg_itis i
    on v.itis_tsn = i.itis_tsn
group by
    v.year
    , v.itis_tsn
    , i.itis_name
)
-- remove confidential
select
    year
    , itis_tsn
    , itis_name
    , price_exvessel
    , price_2022
from
    mainq
where
    n_dealer >= 3
    and n_permit >= 3
order by
    itis_tsn, year