Project

General

Profile

« Previous | Next » 

Revision 12546

fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries

View differences:

vegbien.sql
5981 5981
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS
5982 5982
 SELECT p.projectname AS project_name, 
5983 5983
    l.authorlocationcode AS plot_code
5984
   FROM (((public.location l
5984
   FROM (((public.plot l
5985 5985
   JOIN public.locationevent le ON ((l.location_id = le.location_id)))
5986 5986
   JOIN public.project p ON ((p.project_id = le.project_id)))
5987 5987
   JOIN public.source s ON ((p.source_id = s.source_id)))
......
6022 6022
   JOIN public.source s ON ((p.source_id = s.source_id)))
6023 6023
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6024 6024
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6025
   JOIN public.location l ON ((le.location_id = l.location_id)))
6025
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6026 6026
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6027 6027
   JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
6028 6028
  WHERE (s.shortname = ("current_schema"())::text)
......
6053 6053
   JOIN public.source s ON ((p.source_id = s.source_id)))
6054 6054
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6055 6055
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6056
   JOIN public.location l ON ((le.location_id = l.location_id)))
6056
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6057 6057
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6058 6058
   JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id)))
6059 6059
   JOIN public.plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id)))
......
6084 6084
   JOIN public.source s ON ((p.source_id = s.source_id)))
6085 6085
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6086 6086
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6087
   JOIN public.location l ON ((le.location_id = l.location_id)))
6087
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6088 6088
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6089 6089
   JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
6090 6090
   JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
......
6116 6116
   JOIN public.source s ON ((p.source_id = s.source_id)))
6117 6117
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6118 6118
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6119
   JOIN public.location l ON ((le.location_id = l.location_id)))
6119
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6120 6120
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6121 6121
   JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
6122 6122
   JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
......
6146 6146
   JOIN public.source s ON ((p.source_id = s.source_id)))
6147 6147
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6148 6148
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6149
   JOIN public.location l ON ((le.location_id = l.location_id)))
6149
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6150 6150
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6151 6151
   JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
6152 6152
   JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
......
6178 6178
   JOIN public.source s ON ((p.source_id = s.source_id)))
6179 6179
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6180 6180
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6181
   JOIN public.location l ON ((le.location_id = l.location_id)))
6181
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6182 6182
   JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id)))
6183 6183
   JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id)))
6184 6184
   JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id)))
......
6213 6213
   JOIN public.source s ON ((p.source_id = s.source_id)))
6214 6214
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6215 6215
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6216
   JOIN public.location l ON ((le.location_id = l.location_id)))
6216
   JOIN public.plot l ON ((le.location_id = l.location_id)))
6217 6217
  WHERE (s.shortname = ("current_schema"())::text)
6218 6218
  GROUP BY p.projectname, l.authorlocationcode
6219 6219
  ORDER BY p.projectname, l.authorlocationcode;
......
6231 6231
   JOIN public.source s ON ((p.source_id = s.source_id)))
6232 6232
   JOIN public.locationevent le ON ((p.project_id = le.project_id)))
6233 6233
   JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id)))
6234
   JOIN public.location l ON ((sub_locationevent.location_id = l.location_id)))
6234
   JOIN public.plot l ON ((sub_locationevent.location_id = l.location_id)))
6235 6235
  WHERE (s.shortname = ("current_schema"())::text)
6236 6236
  ORDER BY p.projectname, l.authorlocationcode;
6237 6237

  

Also available in: Unified diff