Revision 9761
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 4561 | 4561 |
-- |
| 4562 | 4562 |
|
| 4563 | 4563 |
CREATE VIEW tnrs_input_name AS |
| 4564 |
WITH "AcceptedTaxon" AS (SELECT DISTINCT "ValidMatchedTaxon"."acceptedScientificName" AS taxonomicname FROM ("TNRS"."ValidMatchedTaxon" LEFT JOIN "TNRS"."AcceptedTaxon" ON (("AcceptedTaxon"."acceptedScientificName" = "ValidMatchedTaxon"."acceptedScientificName"))) WHERE (("ValidMatchedTaxon"."acceptedScientificName" IS NOT NULL) AND ("AcceptedTaxon"."acceptedScientificName" IS NULL))) SELECT "AcceptedTaxon".taxonomicname FROM "AcceptedTaxon" UNION ALL (SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN "TNRS"."ValidMatchedTaxon" ON (("ValidMatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname))) WHERE ((taxonlabel.taxonomicname IS NOT NULL) AND ("ValidMatchedTaxon"."concatenatedScientificName" IS NULL)) EXCEPT SELECT "AcceptedTaxon".taxonomicname FROM "AcceptedTaxon");
|
|
| 4564 |
WITH "AcceptedTaxon" AS (SELECT DISTINCT "MatchedTaxon"."acceptedScientificName" AS taxonomicname FROM ("TNRS"."MatchedTaxon" LEFT JOIN "TNRS"."AcceptedTaxon" ON (("AcceptedTaxon"."acceptedScientificName" = "MatchedTaxon"."acceptedScientificName"))) WHERE (("MatchedTaxon"."acceptedScientificName" IS NOT NULL) AND ("AcceptedTaxon"."acceptedScientificName" IS NULL))) SELECT "AcceptedTaxon".taxonomicname FROM "AcceptedTaxon" UNION ALL (SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname))) WHERE ((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."concatenatedScientificName" IS NULL)) EXCEPT SELECT "AcceptedTaxon".taxonomicname FROM "AcceptedTaxon");
|
|
| 4565 | 4565 |
|
| 4566 | 4566 |
|
| 4567 | 4567 |
-- |
Also available in: Unified diff
bugfix: schemas/vegbien.sql: tnrs_input_name: must anti-join against MatchedTaxon rather than ValidMatchedTaxon to ensure that all of TNRS.tnrs is excluded from the input names. this prevents duplicates from appearing in the TNRS results, which would break the TSV import into TNRS.tnrs. it also prevents no-match names from being scrubbed repeatedly because they were not properly filtered out of the input names.