Project

General

Profile

« Previous | Next » 

Revision 13038

Added by Aaron Marcuse-Kubitza over 10 years ago

validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: use taxon_name*_with_author everywhere instead of custom column names, for consistency

View differences:

validations.sql
72 72 
--
73 73 

  		




  74
  74
  
    
CREATE VIEW _specimens_04_count_of_unique_verbatim_species_with_author AS


  		




  75
  
  
    
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS subspecies


  		




  
  75
  
    
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS taxon_names_with_author


  		




  76
  76
  
    
   FROM "Ecatalog_all"


  		




  77
  77
  
    
  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));


  		




  78
  78
  
    

  		




  ......




  91
  91
  
    
--


  		




  92
  92
  
    

  		




  93
  93
  
    
CREATE VIEW _specimens_05_list_of_unique_verbatim_species_with_author AS


  		




  94
  
  
    
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS subspecies


  		




  
  94
  
    
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS taxon_name_with_author


  		




  95
  95
  
    
   FROM "Ecatalog_all"


  		




  96
  96
  
    
  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));


  		




  97
  97
  
    

  		




  ......




  110
  110
  
    
--


  		




  111
  111
  
    

  		




  112
  112
  
    
CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS


  		




  113
  
  
    
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet", "Ecatalog_all".subspecies)) AS "specificEpithet"


  		




  
  113
  
    
 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet", "Ecatalog_all".subspecies)) AS taxon_names_with_author


  		




  114
  114
  
    
   FROM "Ecatalog_all"


  		




  115
  115
  
    
  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));


  		




  116
  116
  
    

  		




  ......




  129
  129
  
    
--


  		




  130
  130
  
    

  		




  131
  131
  
    
CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS


  		




  132
  
  
    
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS "specificEpithet"


  		




  
  132
  
    
 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS taxon_name_with_author


  		




  133
  133
  
    
   FROM "Ecatalog_all"


  		




  134
  134
  
    
  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));


  		




  135
  135
  
    

  		




  ......




  148
  148
  
    
--


  		




  149
  149
  
    

  		




  150
  150
  
    
CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS


  		




  151
  
  
    
 SELECT count(DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text))))) AS "fullScientificNames"


  		




  
  151
  
    
 SELECT count(DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text))))) AS taxon_names_with_author


  		




  152
  152
  
    
   FROM "Ecatalog_all"


  		




  153
  153
  
    
  WHERE ("Ecatalog_all".genus IS NOT NULL);


  		




  154
  154
  
    

  		




  ......




  167
  167
  
    
--


  		




  168
  168
  
    

  		




  169
  169
  
    
CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS


  		




  170
  
  
    
 SELECT DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text)))) AS "fullScientificName"


  		




  
  170
  
    
 SELECT DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text)))) AS taxon_name_with_author


  		




  171
  171
  
    
   FROM "Ecatalog_all"


  		




  172
  172
  
    
  WHERE ("Ecatalog_all".genus IS NOT NULL);


  		




  173
  173
  
    

  		












Also available in:  Unified diff