Project

General

Profile

« Previous | Next » 

Revision 14110

inputs/.TNRS/schema.sql: MatchedTaxon: taxon_best_match: don't alias to `s` since this is no longer a nested select

View differences:

trunk/inputs/.TNRS/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
--
trunk/schemas/vegbien.sql
19506 19506
--
19507 19507

  
19508 19508
CREATE VIEW "MatchedTaxon" AS
19509
 SELECT s.batch,
19510
    s.match_num,
19511
    s."Name_number",
19512
    s."Name_submitted",
19513
    s."Overall_score",
19514
    s."Name_matched",
19515
    s."Name_matched_rank",
19516
    s."Name_score",
19517
    s."Name_matched_author",
19518
    s."Name_matched_url",
19519
    s."Author_matched",
19520
    s."Author_score",
19521
    s."Family_matched",
19522
    s."Family_score",
19523
    s."Name_matched_accepted_family",
19524
    s."Genus_matched",
19525
    s."Genus_score",
19526
    s."Specific_epithet_matched",
19527
    s."Specific_epithet_score",
19528
    s."Infraspecific_rank",
19529
    s."Infraspecific_epithet_matched",
19530
    s."Infraspecific_epithet_score",
19531
    s."Infraspecific_rank_2",
19532
    s."Infraspecific_epithet_2_matched",
19533
    s."Infraspecific_epithet_2_score",
19534
    s."Annotations",
19535
    s."Unmatched_terms",
19536
    s."Taxonomic_status",
19537
    s."Accepted_name",
19538
    s."Accepted_name_author",
19539
    s."Accepted_name_rank",
19540
    s."Accepted_name_url",
19541
    s."Accepted_name_species",
19542
    s."Accepted_name_family",
19543
    s."Selected",
19544
    s."Source",
19545
    s."Warnings",
19546
    s."Accepted_name_lsid",
19547
    s.is_valid_match,
19548
    s.scrubbed_unique_taxon_name,
19549
    map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
19509
 SELECT taxon_best_match.batch,
19510
    taxon_best_match.match_num,
19511
    taxon_best_match."Name_number",
19512
    taxon_best_match."Name_submitted",
19513
    taxon_best_match."Overall_score",
19514
    taxon_best_match."Name_matched",
19515
    taxon_best_match."Name_matched_rank",
19516
    taxon_best_match."Name_score",
19517
    taxon_best_match."Name_matched_author",
19518
    taxon_best_match."Name_matched_url",
19519
    taxon_best_match."Author_matched",
19520
    taxon_best_match."Author_score",
19521
    taxon_best_match."Family_matched",
19522
    taxon_best_match."Family_score",
19523
    taxon_best_match."Name_matched_accepted_family",
19524
    taxon_best_match."Genus_matched",
19525
    taxon_best_match."Genus_score",
19526
    taxon_best_match."Specific_epithet_matched",
19527
    taxon_best_match."Specific_epithet_score",
19528
    taxon_best_match."Infraspecific_rank",
19529
    taxon_best_match."Infraspecific_epithet_matched",
19530
    taxon_best_match."Infraspecific_epithet_score",
19531
    taxon_best_match."Infraspecific_rank_2",
19532
    taxon_best_match."Infraspecific_epithet_2_matched",
19533
    taxon_best_match."Infraspecific_epithet_2_score",
19534
    taxon_best_match."Annotations",
19535
    taxon_best_match."Unmatched_terms",
19536
    taxon_best_match."Taxonomic_status",
19537
    taxon_best_match."Accepted_name",
19538
    taxon_best_match."Accepted_name_author",
19539
    taxon_best_match."Accepted_name_rank",
19540
    taxon_best_match."Accepted_name_url",
19541
    taxon_best_match."Accepted_name_species",
19542
    taxon_best_match."Accepted_name_family",
19543
    taxon_best_match."Selected",
19544
    taxon_best_match."Source",
19545
    taxon_best_match."Warnings",
19546
    taxon_best_match."Accepted_name_lsid",
19547
    taxon_best_match.is_valid_match,
19548
    taxon_best_match.scrubbed_unique_taxon_name,
19549
    map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
19550 19550
        CASE
19551
            WHEN (s."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, s."Accepted_name_family", s."Unmatched_terms")
19552
            WHEN (s."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, s."Accepted_name", s."Unmatched_terms")
19553
            ELSE s."Accepted_name_species"
19551
            WHEN (taxon_best_match."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name_family", taxon_best_match."Unmatched_terms")
19552
            WHEN (taxon_best_match."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name", taxon_best_match."Unmatched_terms")
19553
            ELSE taxon_best_match."Accepted_name_species"
19554 19554
        END AS accepted_morphospecies_binomial
19555
   FROM taxon_best_match s;
19555
   FROM taxon_best_match;
19556 19556

  
19557 19557

  
19558 19558
--

Also available in: Unified diff