Revision 7366
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 4175 | 4175 |
-- |
| 4176 | 4176 |
|
| 4177 | 4177 |
CREATE VIEW taxon_trait_view AS |
| 4178 |
SELECT taxonlabel.taxonomicname AS "scientificName", trait.name AS "measurementType", trait.value AS "measurementValue", trait.units AS "measurementUnit" FROM ((((trait LEFT JOIN taxonoccurrence USING (taxonoccurrence_id)) LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent))) LEFT JOIN taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel USING (taxonlabel_id));
|
|
| 4178 |
SELECT COALESCE(taxonlabel.taxonomicname, taxonverbatim.taxonomicname) AS "scientificName", trait.name AS "measurementType", trait.value AS "measurementValue", trait.units AS "measurementUnit" FROM ((((trait LEFT JOIN taxonoccurrence USING (taxonoccurrence_id)) LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent))) LEFT JOIN taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel USING (taxonlabel_id));
|
|
| 4179 | 4179 |
|
| 4180 | 4180 |
|
| 4181 | 4181 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxon_trait_view: Added workaround for bug where the taxonlabel's taxonomicname (concatenated taxonomicname) is occasionally not populated due to a taxonlabel constraint violation, by using the taxonverbatim's taxonomicname instead in these cases. This bug, which appeared in the r7317 import, is so far not reproducible (tested on Mac OS X), so its cause is unknown, but may be caused by a bug in functions._merge_prefix(), which is run on the taxonlabel's taxonomicname but not the taxonverbatim's taxonomicname.