|
|
6 squ_ce Views
The following are listings of the single and multi-table viewsin the squ_ce database, including attribute names, datatypes (and any range restrictions), and comments.
6.1 View 1: v_events
Comment: Containsthe current version of events, including fishing, recording ofenvironmental information and daily processing events.
| View: |
select attr 'event_key', attr 'data_ver', attr 'form_type', attr 'form_no', attr 'event_type', attr 'start_time', attr 'fin_time', attr 'validity', attr 'vess_key', attr 'start_lat', attr 'start_long', attr 'start_EW', attr 'fin_lat', attr 'fin_long', attr 'fin_EW', attr 'op_ev_type', attr 'loc_area', attr 'fma', attr 'stat_area' from 't_events' 'e' where (attr 'data_ver' = (select max (attr 'data_ver') from 't_events' 'm' where (attr 'm'.'event_key' = attr 'e'.'event_key'))) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| form_type |
smallint |
|
| form_no |
decimal(12,0) |
No |
| event_type |
character(3,1) |
|
| start_time |
time(0) |
No |
| fin_time |
time(0) |
|
| validity |
character(7,1) |
|
| vess_key |
longinteger |
No |
| start_lat |
decimal(6,4) |
|
| start_long |
decimal(7,4) |
|
| start_EW |
character(1,1) |
|
| fin_lat |
decimal(6,4) |
|
| fin_long |
decimal(7,4) |
|
| fin_EW |
character(1,1) |
|
| op_ev_type |
character(3,1) |
No |
| loc_area |
character(7,1) |
|
| fma |
character(7,1) |
|
| stat_area |
integer |
|
6.2 View 2: v_fish_evnts
Comment: Containsthe latest version of effort and overall catch for each fishingevent.
| View: |
select attr 'event_key', attr 'data_ver', attr 'shot_no', attr 'meth_code', attr 'tar_species', attr 'duration', attr 'event_wt', attr 'validity', attr 'bttm_depth', attr 'eff_depth', attr 'eff_height', attr 'num_single', attr 'eff_width', attr 'trawl_speed', attr 'num_double' from 't_fish_evnts' 'f' where (attr 'data_ver' in (select max (attr 'data_ver') from 't_fish_evnts' 'm' where (attr 'm'.'event_key' = attr 'f'.'event_key'))) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| shot_no |
smallint |
|
| meth_code |
character(3,1) |
|
| tar_species |
character(3,1) |
|
| duration |
decimal(3,1) |
|
| event_wt |
decimal(7,1) |
|
| validity |
character(7,1) |
|
| bttm_depth |
longinteger |
|
| eff_depth |
longinteger |
|
| eff_height |
decimal(3,1) |
|
| num_single |
smallint |
|
| eff_width |
decimal(4,1) |
|
| trawl_speed |
decimal(3,1) |
|
| num_double |
smallint |
|
6.3 View 3: v_catches
Comment: Currentversion of catch weights for each species caught.
| View: |
select attr 'event_key', attr 'item_seq_no', attr 'data_ver', attr 'spp', attr 'catch_wt', attr 'validity' from 't_catches' 'c' where (attr 'data_ver' in (select max (attr 'data_ver') from 't_catches' 'm' where (attr 'm'.'event_key' = attr 'c'.'event_key'))) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| item_seq_no |
smallint |
No |
| data_ver |
smallint |
No |
| spp |
character(3,1) |
|
| catch_wt |
decimal(7,1) |
|
| validity |
character(7,1) |
|
6.4 View 4: v_proc_evnts
Comment: Currentversion of daily processing records of each species. Containsprocessing data and calculated greenweight.
| View: |
select attr 'event_key', attr 'seq_no', attr 'data_ver', attr 'spp', attr 'act_type', attr 'proc_state', attr 'unit_type', attr 'unit_num', attr 'unit_wt', attr 'conv_fact', attr 'green_wt', attr 'green_wt_type”, attr 'proc_wt', attr 'proc_wt_type', attr "validity", attr 'stock_code' from 't_proc_evnts' 'p' where (attr 'data_ver' in (select max (attr 'data_ver') from 't_proc_evnts' 'm' where (attr 'm'.'event_key' = attr 'p'.'event_key'))) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| seq_no |
smallint |
No |
| data_ver |
smallint |
No |
| spp |
character(3,1) |
No |
| act_type |
character(3,1) |
|
| proc_state |
character(4,1) |
No |
| unit_type |
character(3,1) |
|
| unit_num |
integer |
|
| unit_wt |
decimal(5,2) |
|
| conv_fact |
decimal(5,3) |
|
| green_wt |
decimal(6,1) |
|
| green_wt_type |
character(3,1) |
|
| proc_wt |
decimal(6,1) |
|
| proc_wt_type |
character(3,1) |
|
| validity |
character(7,1) |
|
| stock_code |
character(7,1) |
|
6.5 View 5: v_environ
Comment: Latest version of observations of environmental conditions. Time and position for each set of observations (each environmental event) can be found in t_events. For jigging environmental observations are made at 0100 hr and for trawling they are made before the first shot of the day.
| View: |
select attr 'event_key', attr 'data_ver', attr 'sst', attr
'bttm_temp', attr 'speed', attr 'direction', attr 'bttm_depth', attr 'validity' from 't_environ' 'v' where (attr 'data_ver' in (selectmax (attr 'data_ver') from 't_environ' 'm' where (attr 'm'.'event_key' = attr 'v'.'event_key')))View: v_environ |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| sst |
decimal(3,1) |
|
| bttm_temp |
decimal(3,1) |
|
| speed |
longinteger |
|
| direction |
longinteger |
|
| bttm_depth |
longinteger |
|
| validity |
character(7,1) |
|
6.6 View 6: v_fish
Comment: This view joins the latest versions of the tables t_events and t_fish_events. The result is time and location data associated with data for each fishing event.
| View: |
select attr 'e'.'event_key', attr 'e'.'data_ver', attr 'form_type', attr 'form_no', attr 'event_type', attr 'start_time', attr 'fin_time', attr 'e'.'validity' print 'evt_validity', attr 'vess_key', attr 'start_lat', attr 'start_long', attr 'start_EW', attr 'fin_lat', attr 'fin_long', attr 'fin_EW', attr 'op_ev_type', attr 'loc_area', attr 'fma', attr 'stat_area', attr 'shot_no', attr 'meth_code', attr 'tar_species', attr 'duration', attr 'event_wt', attr 'f'.'validity' print 'fsh_validity', attr 'bttm_depth', attr 'eff_depth', attr 'eff_height', attr 'num_single', attr 'eff_width', attr 'trawl_speed', attr 'num_double' from 't_events' 'e', 't_fish_evnts' 'f' where ((((attr 'op_ev_type' = 'F' and attr 'e'.'data_ver' = (select max (attr 'data_ver') from 't_events' 'm' where (attr 'm'.'event_key' = attr 'e'.'event_key'))) and attr
'f'.'event_key' = attr 'e'.'event_key') and attr 'f'.'data_ver' = attr 'e'.'data_ver')) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| form_type |
smallint |
|
| form_no |
decimal(12,0) |
No |
| event_type |
character(3,1) |
|
| start_time |
time(0) |
No |
| fin_time |
time(0) |
|
| evt_validity |
character(7,1) |
|
| vess_key |
longinteger No |
|
| start_lat |
decimal(6,4) |
|
| start_long |
decimal(7,4) |
|
| start_EW |
character(1,1) |
|
| fin_lat |
decimal(6,4) |
|
| fin_long |
decimal(7,4) |
|
| fin_EW |
character(1,1) |
|
| op_ev_type |
character(3,1) |
No |
| loc_area |
character(7,1) |
|
| fma |
character(7,1) |
|
| stat_area |
integer |
|
| shot_no |
smallint |
|
| meth_code |
character(3,1) |
|
| tar_species |
character(3,1) |
|
| duration |
decimal(3,1) |
|
| event_wt |
decimal(7,1) |
|
| fsh_validity |
character(7,1) |
|
| bttm_depth |
longinteger |
|
| eff_depth |
longinteger |
|
| eff_height |
decimal(3,1) |
|
| num_single |
smallint |
|
| eff_width |
decimal(4,1) |
|
| trawl_speed |
decimal(3,1) |
|
| num_double |
smallint |
|
6.7 View 7: v_proc
Comment: This viewjoins the latest versions of the tables t_events and t_proc_evnts.The result is time and location data associated with eachprocessing event.
| View: |
select attr 'e'.'event_key', attr 'e'.'data_ver', attr 'form_type', attr 'form_no', attr 'event_type', attr 'start_time', attr 'fin_time', attr 'e'.'validity' print 'evt_validity', attr 'vess_key', attr 'start_lat', attr 'start_long', attr 'start_EW', attr 'fin_lat', attr 'fin_long', attr 'fin_EW', attr 'op_ev_type', attr 'loc_area', attr 'fma', attr 'stat_area', attr 'seq_no', attr 'spp', attr 'act_type', attr 'proc_state', attr 'unit_type', attr 'unit_num', attr 'unit_wt', attr 'conv_fact', attr 'green_wt', attr 'green_wt_type', attr 'proc_wt', attr 'proc_wt_type', attr 'p'.'validity' print 'proc_validity', attr 'stock_code' from 't_events' 'e', 't_proc_evnts' 'p' where ((((attr 'op_ev_type' = 'P' and attr 'e'.'data_ver' = (select max (attr 'data_ver') from 't_events' 'm' where (attr 'm'.'event_key' = attr 'e'.'event_key'))) and attr 'p'.'event_key' = attr 'e'.'event_key') and attr 'p'.'data_ver' = attr 'e'.'data_ver')) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| form_type |
smallint |
|
| form_no |
decimal(12,0) |
No |
| event_type |
character(3,1) |
|
| start_time |
time(0) |
No |
| fin_time |
time(0) |
|
| evt_validity |
character(7,1) |
|
| vess_key |
longinteger |
No |
| start_lat |
decimal(6,4) |
|
| start_long |
decimal(7,4) |
|
| start_EW |
character(1,1) |
|
| fin_lat |
decimal(6,4) |
|
| fin_long |
decimal(7,4) |
|
| fin_EW |
character(1,1) |
|
| op_ev_type |
character(3,1) |
No |
| loc_area |
character(7,1) |
|
| fma |
character(7,1) |
|
| stat_area |
integer |
|
| seq_no |
smallint |
No |
| spp |
character(3,1) |
No |
| act_type |
character(3,1) |
|
| proc_state |
character(4,1) |
No |
| unit_type |
character(3,1) |
|
| unit_num |
integer |
|
| unit_wt |
decimal(5,2) |
|
| conv_fact |
decimal(5,3) |
|
| green_wt |
decimal(6,1) |
|
| green_wt_type |
character(3,1) |
|
| proc_wt |
decimal(6,1) |
|
| proc_wt_type |
character(3,1) |
|
| proc_validity |
character(7,1) |
|
| stock_code |
character(7,1) |
|
6.8 View 8: v_env
Comment: This view joins the current versions of the tables t_events and t_environ. The result is time and location data associated with data for each environmental event.
| View: |
select attr 'e'.'event_key', attr 'e'.'data_ver', attr 'form_type', attr 'form_no', attr 'event_type', attr 'start_time', attr 'fin_time', attr 'e'.'validity' print 'evt_validity', attr 'vess_key', attr 'start_lat', attr 'start_long', attr 'start_EW', attr 'fin_lat', attr 'fin_long', attr 'fin_EW', attr 'op_ev_type', attr 'loc_area', attr 'fma', attr 'stat_area', attr 'sst', attr
'bttm_temp', attr 'speed', attr 'direction', attr 'bttm_depth', attr 'v'.'validity' print 'env_validity' from 't_events' 'e', 't_environ' 'v' where ((((attr 'op_ev_type' = 'E' and attr 'e'.'data_ver' = (select max (attr 'data_ver') from 't_events' 'm' where (attr 'm'.'event_key' = attr 'e'.'event_key'))) and attr 'v'.'event_key' = attr 'e'.'event_key') and attr 'v'.'data_ver' = attr 'e'.'data_ver')) |
| Attributes |
DataType |
Null? |
| event_key |
decimal(12,0) |
No |
| data_ver |
smallint |
No |
| form_type |
smallint |
|
| form_no |
decimal(12,0) |
No |
| event_type |
character(3,1) |
|
| start_time |
time(0) |
No |
| fin_time |
time(0) |
|
| evt_validity |
character(7,1) |
|
| vess_key |
longinteger |
No |
| start_lat |
decimal(6,4) |
|
| start_long |
decimal(7,4) |
|
| start_EW |
character(1,1) |
|
| fin_lat |
decimal(6,4) |
|
| fin_long |
decimal(7,4) |
|
| fin_EW |
character(1,1) |
|
| op_ev_type |
character(3,1) |
No |
| loc_area |
character(7,1) |
|
| fma |
character(7,1) |
|
| stat_area |
integer |
|
| sst |
decimal(3,1) |
|
| bttm_temp |
decimal(3,1) |
|
| speed |
longinteger |
|
| direction |
longinteger |
|
| bttm_depth |
longinteger |
|
| env_validity |
character(7,1) |
|
6.9 View 9: v_jigs
Comment: This viewjoins tables t_fish_evnts, t_proc_evnts and t_environ, usingattributes event_key, form_no, form_type and data_ver from thetable t_events. It thus represents all the data from any night'sjigging from a single vessel.
| View: |
select attr 'f'.'event_key' print 'fsh_event_key',attr 'f'.'data_ver' print 'fsh_data_ver', attr 'f'.'form_type',attr 'f'.'form_no', attr 'f'.'event_type' print 'fsh_event_type',attr 'f'.'start_time' print 'fsh_start_time', attr 'f'.'fin_time'print 'fsh_fin_time', attr 'f'.'evt_validity' print'fsh_evt_validity', attr 'f'.'vess_key' print 'fsh_vess_key', attr'f'.'start_lat' print 'fsh_start_lat', attr 'f'.'start_long' print'fsh_start_long', attr 'f'.'start_EW' print 'fsh_start_EW', attr'f'.'fin_lat' print 'fsh_fin_lat', attr 'f'.'fin_long' print'fsh_fin_long', attr 'f'.'fin_EW' print 'fsh_fin_EW', attr'f'.'op_ev_type' print 'fsh_op_ev_type', attr 'f'.'loc_area' print'fsh_loc_area', attr 'f'.'fma' print 'fsh_fma', attr'f'.'stat_area' print 'fsh_stat_area', attr 'shot_no', attr'meth_code', attr 'tar_species', attr 'duration', attr 'event_wt',attr 'fsh_validity', attr 'bttm_depth', attr 'eff_depth', attr'eff_height', attr 'num_single', attr 'eff_width', attr'trawl_speed', attr 'num_double', attr 'p'.'event_key' print'prc_event_key', attr 'p'.'data_ver' print 'prc_data_ver', attr'p'. 'event_type' print 'prc_event_type', attr 'p'.'start_time'print 'prc_start_time', attr 'p'.'fin_time' print 'prc_fin_time',attr 'p'.'evt_validity' print 'prc_evt_validity', attr'p'.'vess_key' print 'prc_vess_key', attr 'p'.'start_lat' print'prc_start_lat', attr 'p'.'start_long' print 'prc_start_long', attr'p'.'start_EW' print 'prc_start_EW', attr 'p'.'fin_lat' print'prc_fin_lat', attr 'p'.'fin_long' print 'prc_fin_long', attr'p'.'fin_EW' print 'prc_fin_EW', attr 'p'.'op_ev_type' print'prc_op_ev_type', attr 'p'.'loc_area' print 'prc_loc_area', attr'p'.'fma' print 'prc_fma', attr 'p'.'stat_area' print'prc_stat_area', attr 'seq_no', attr 'spp', attr 'act_type', attr'proc_state', attr 'unit_type', attr 'unit_num', attr 'unit_wt',attr 'conv_fact', attr 'green_wt', attr 'green_wt_type', attr'proc_wt', attr 'proc_wt_type', attr 'proc_validity', attr'stock_code' from 'v_fish' 'f', 'v_proc' 'p' where (((attr'f'.'meth_code' = 'SQJ' and attr 'p'.'form_no' = attr 'f'.'form_no') and attr 'p'.'form_type' = attr 'f'.'form_type')) |
| Attributes |
Data Type |
Null? |
| fsh_event_key |
decimal(12,0) |
No |
| fsh_data_ver |
smallint |
No |
| form_type |
smallint |
|
| form_no |
decimal(12,0) |
No |
| fsh_event_type |
character(3,1) |
|
| fsh_start_time |
time(0) |
No |
| fsh_fin_time |
time(0) |
|
| fsh_evt_validity |
character(7,1) |
|
| fsh_vess_key |
longinteger |
No |
| fsh_start_lat |
decimal(6,4) |
|
| fsh_start_long |
decimal(7,4) |
|
| fsh_start_EW |
character(1,1) |
|
| fsh_fin_lat |
decimal(6,4) |
|
| fsh_fin_long |
decimal(7,4) |
|
| fsh_fin_EW |
character(1,1) |
|
| fsh_op_ev_type |
character(3,1) |
No |
| fsh_loc_area |
character(7,1) |
|
| fsh_fma |
character(7,1) |
|
| fsh_stat_area |
integer |
|
| shot_no |
smallint |
|
| meth_code |
character(3,1) |
|
| tar_species |
character(3,1) |
|
| duration |
decimal(3,1) |
|
| event_wt |
decimal(7,1) |
|
| fsh_validity |
character(7,1) |
|
| bttm_depth |
longinteger |
|
| eff_depth |
longinteger |
|
| eff_height |
decimal(3,1) |
|
| num_single |
smallint |
|
| eff_width |
decimal(4,1) |
|
| trawl_speed |
decimal(3,1) |
|
| num_double |
smallint |
|
| prc_event_key |
decimal(12,0) |
No |
| prc_data_ver |
smallint |
No |
| prc_event_type |
character(3,1) |
|
| prc_start_time |
time(0) |
No |
| prc_fin_time |
time(0) |
|
| prc_evt_validity |
character(7,1) |
|
| prc_vess_key |
longinteger |
No |
| prc_start_lat |
decimal(6,4) |
|
| prc_start_long |
decimal(7,4) |
|
| prc_start_EW |
character(1,1) |
|
| prc_fin_lat |
decimal(6,4) |
|
| prc_fin_long |
decimal(7,4) |
|
| prc_fin_EW |
character(1,1) |
|
| prc_op_ev_type |
character(3,1) |
No |
| prc_loc_area |
character(7,1) |
|
| prc_fma |
character(7,1) |
|
| prc_stat_area |
integer |
|
| seq_no |
smallint |
No |
| spp |
character(3,1) |
No |
| act_type |
character(3,1) |
|
| proc_state |
character(4,1) |
No |
| unit_type |
character(3,1) |
|
| unit_num |
integer |
|
| unit_wt |
decimal(5,2) |
|
| conv_fact |
decimal(5,3) |
|
| green_wt |
decimal(6,1) |
|
| green_wt_type |
character(3,1) |
|
| proc_wt |
decimal(6,1) |
|
| proc_wt_type |
character(3,1) |
|
| proc_validity |
character(7,1) |
|
| stock_code |
character(7,1) |
|
|
|