Revision 14110
Added by Aaron Marcuse-Kubitza over 10 years ago
| schema.sql | ||
|---|---|---|
| 394 | 394 |
-- |
| 395 | 395 |
|
| 396 | 396 |
CREATE VIEW "MatchedTaxon" AS |
| 397 |
SELECT s.batch,
|
|
| 398 |
s.match_num,
|
|
| 399 |
s."Name_number",
|
|
| 400 |
s."Name_submitted",
|
|
| 401 |
s."Overall_score",
|
|
| 402 |
s."Name_matched",
|
|
| 403 |
s."Name_matched_rank",
|
|
| 404 |
s."Name_score",
|
|
| 405 |
s."Name_matched_author",
|
|
| 406 |
s."Name_matched_url",
|
|
| 407 |
s."Author_matched",
|
|
| 408 |
s."Author_score",
|
|
| 409 |
s."Family_matched",
|
|
| 410 |
s."Family_score",
|
|
| 411 |
s."Name_matched_accepted_family",
|
|
| 412 |
s."Genus_matched",
|
|
| 413 |
s."Genus_score",
|
|
| 414 |
s."Specific_epithet_matched",
|
|
| 415 |
s."Specific_epithet_score",
|
|
| 416 |
s."Infraspecific_rank",
|
|
| 417 |
s."Infraspecific_epithet_matched",
|
|
| 418 |
s."Infraspecific_epithet_score",
|
|
| 419 |
s."Infraspecific_rank_2",
|
|
| 420 |
s."Infraspecific_epithet_2_matched",
|
|
| 421 |
s."Infraspecific_epithet_2_score",
|
|
| 422 |
s."Annotations",
|
|
| 423 |
s."Unmatched_terms",
|
|
| 424 |
s."Taxonomic_status",
|
|
| 425 |
s."Accepted_name",
|
|
| 426 |
s."Accepted_name_author",
|
|
| 427 |
s."Accepted_name_rank",
|
|
| 428 |
s."Accepted_name_url",
|
|
| 429 |
s."Accepted_name_species",
|
|
| 430 |
s."Accepted_name_family",
|
|
| 431 |
s."Selected",
|
|
| 432 |
s."Source",
|
|
| 433 |
s."Warnings",
|
|
| 434 |
s."Accepted_name_lsid",
|
|
| 435 |
s.is_valid_match,
|
|
| 436 |
s.scrubbed_unique_taxon_name,
|
|
| 437 |
map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
|
|
| 397 |
SELECT taxon_best_match.batch,
|
|
| 398 |
taxon_best_match.match_num,
|
|
| 399 |
taxon_best_match."Name_number",
|
|
| 400 |
taxon_best_match."Name_submitted",
|
|
| 401 |
taxon_best_match."Overall_score",
|
|
| 402 |
taxon_best_match."Name_matched",
|
|
| 403 |
taxon_best_match."Name_matched_rank",
|
|
| 404 |
taxon_best_match."Name_score",
|
|
| 405 |
taxon_best_match."Name_matched_author",
|
|
| 406 |
taxon_best_match."Name_matched_url",
|
|
| 407 |
taxon_best_match."Author_matched",
|
|
| 408 |
taxon_best_match."Author_score",
|
|
| 409 |
taxon_best_match."Family_matched",
|
|
| 410 |
taxon_best_match."Family_score",
|
|
| 411 |
taxon_best_match."Name_matched_accepted_family",
|
|
| 412 |
taxon_best_match."Genus_matched",
|
|
| 413 |
taxon_best_match."Genus_score",
|
|
| 414 |
taxon_best_match."Specific_epithet_matched",
|
|
| 415 |
taxon_best_match."Specific_epithet_score",
|
|
| 416 |
taxon_best_match."Infraspecific_rank",
|
|
| 417 |
taxon_best_match."Infraspecific_epithet_matched",
|
|
| 418 |
taxon_best_match."Infraspecific_epithet_score",
|
|
| 419 |
taxon_best_match."Infraspecific_rank_2",
|
|
| 420 |
taxon_best_match."Infraspecific_epithet_2_matched",
|
|
| 421 |
taxon_best_match."Infraspecific_epithet_2_score",
|
|
| 422 |
taxon_best_match."Annotations",
|
|
| 423 |
taxon_best_match."Unmatched_terms",
|
|
| 424 |
taxon_best_match."Taxonomic_status",
|
|
| 425 |
taxon_best_match."Accepted_name",
|
|
| 426 |
taxon_best_match."Accepted_name_author",
|
|
| 427 |
taxon_best_match."Accepted_name_rank",
|
|
| 428 |
taxon_best_match."Accepted_name_url",
|
|
| 429 |
taxon_best_match."Accepted_name_species",
|
|
| 430 |
taxon_best_match."Accepted_name_family",
|
|
| 431 |
taxon_best_match."Selected",
|
|
| 432 |
taxon_best_match."Source",
|
|
| 433 |
taxon_best_match."Warnings",
|
|
| 434 |
taxon_best_match."Accepted_name_lsid",
|
|
| 435 |
taxon_best_match.is_valid_match,
|
|
| 436 |
taxon_best_match.scrubbed_unique_taxon_name,
|
|
| 437 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
| 438 | 438 |
CASE |
| 439 |
WHEN (s."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, s."Accepted_name_family", s."Unmatched_terms")
|
|
| 440 |
WHEN (s."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, s."Accepted_name", s."Unmatched_terms")
|
|
| 441 |
ELSE s."Accepted_name_species"
|
|
| 439 |
WHEN (taxon_best_match."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name_family", taxon_best_match."Unmatched_terms")
|
|
| 440 |
WHEN (taxon_best_match."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name", taxon_best_match."Unmatched_terms")
|
|
| 441 |
ELSE taxon_best_match."Accepted_name_species"
|
|
| 442 | 442 |
END AS accepted_morphospecies_binomial |
| 443 |
FROM taxon_best_match s;
|
|
| 443 |
FROM taxon_best_match; |
|
| 444 | 444 |
|
| 445 | 445 |
|
| 446 | 446 |
-- |
Also available in: Unified diff
inputs/.TNRS/schema.sql: MatchedTaxon: taxon_best_match: don't alias to `s` since this is no longer a nested select