Project

General

Profile

« Previous | Next » 

Revision 13040

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: _specimens_04_count_of_unique_verbatim_species_with_author, _specimens_05_list_of_unique_verbatim_species_with_author: switched back to original names because #6,7 now do the same thing as #4,5, so we should include the differing result set of #4,5 for datasources that provide it

View differences:

trunk/inputs/NY/validations.sql
68 68 

  		




  69
  69
  
    

  		




  70
  70
  
    
--


  		




  71
  
  
    
-- Name: _specimens_04_count_of_unique_verbatim_species_with_author; Type: VIEW; Schema: NY; Owner: -


  		




  
  71
  
    
-- Name: _specimens_04_count_of_unique_verbatim_species_without_author; Type: VIEW; Schema: NY; Owner: -


  		




  72
  72
  
    
--


  		




  73
  73
  
    

  		




  74
  
  
    
CREATE VIEW _specimens_04_count_of_unique_verbatim_species_with_author AS


  		




  
  74
  
    
CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS


  		




  75
  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
  
    

  		




  79
  79
  
    

  		




  80
  80
  
    
--


  		




  81
  
  
    
-- Name: VIEW _specimens_04_count_of_unique_verbatim_species_with_author; Type: COMMENT; Schema: NY; Owner: -


  		




  
  81
  
    
-- Name: VIEW _specimens_04_count_of_unique_verbatim_species_without_author; Type: COMMENT; Schema: NY; Owner: -


  		




  82
  82
  
    
--


  		




  83
  83
  
    

  		




  84
  
  
    
COMMENT ON VIEW _specimens_04_count_of_unique_verbatim_species_with_author IS '


  		




  
  84
  
    
COMMENT ON VIEW _specimens_04_count_of_unique_verbatim_species_without_author IS '


  		




  85
  85
  
    
Check: should return 1 row


  		




  86
  86
  
    
';


  		




  87
  87
  
    

  		




  88
  88
  
    

  		




  89
  89
  
    
--


  		




  90
  
  
    
-- Name: _specimens_05_list_of_unique_verbatim_species_with_author; Type: VIEW; Schema: NY; Owner: -


  		




  
  90
  
    
-- Name: _specimens_05_list_of_verbatim_species_excluding_author; Type: VIEW; Schema: NY; Owner: -


  		




  91
  91
  
    
--


  		




  92
  92
  
    

  		




  93
  
  
    
CREATE VIEW _specimens_05_list_of_unique_verbatim_species_with_author AS


  		




  
  93
  
    
CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS


  		




  94
  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
  
    

  		




  98
  98
  
    

  		




  99
  99
  
    
--


  		




  100
  
  
    
-- Name: VIEW _specimens_05_list_of_unique_verbatim_species_with_author; Type: COMMENT; Schema: NY; Owner: -


  		




  
  100
  
    
-- Name: VIEW _specimens_05_list_of_verbatim_species_excluding_author; Type: COMMENT; Schema: NY; Owner: -


  		




  101
  101
  
    
--


  		




  102
  102
  
    

  		




  103
  
  
    
COMMENT ON VIEW _specimens_05_list_of_unique_verbatim_species_with_author IS '


  		




  
  103
  
    
COMMENT ON VIEW _specimens_05_list_of_verbatim_species_excluding_author IS '


  		




  104
  104
  
    
Check: should return 3335 rows


  		




  105
  105
  
    
';


  		




  106
  106
  
    

  		












  

    
      trunk/validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql
    
  





  29
  29
  
    
Check: should return same number of rows


  		




  30
  30
  
    
*/


  		




  31
  31
  
    

  		




  32
  
  
    
-- _specimens_04_count_of_unique_verbatim_species_with_author


  		




  
  32
  
    
-- _specimens_04_count_of_unique_verbatim_species_without_author


  		




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


  		




  34
  34
  
    
   FROM "Ecatalog_all"


  		




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


  		




  ......




  37
  37
  
    
Check: should return 1 row


  		




  38
  38
  
    
*/


  		




  39
  39
  
    

  		




  40
  
  
    
-- _specimens_05_list_of_unique_verbatim_species_with_author


  		




  
  40
  
    
-- _specimens_05_list_of_verbatim_species_excluding_author


  		




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


  		




  42
  42
  
    
   FROM "Ecatalog_all"


  		




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


  		












  

    
      trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
    
  





  33
  33
  
    
AND family IS NOT NULL


  		




  34
  34
  
    
;


  		




  35
  35
  
    

  		




  36
  
  
    
CREATE VIEW _specimens_04_count_of_unique_verbatim_species_with_author AS


  		




  
  36
  
    
CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS


  		




  37
  37
  
    
SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author


  		




  38
  38
  
    
FROM taxonlabel


  		




  39
  39
  
    
WHERE taxonlabel.source_id = (SELECT source_by_shortname('NY'))


  		




  40
  40
  
    
AND taxonlabel.taxonomicname IS NOT NULL


  		




  41
  41
  
    
;


  		




  42
  42
  
    

  		




  43
  
  
    
CREATE VIEW _specimens_05_list_of_unique_verbatim_species_with_author AS


  		




  
  43
  
    
CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS


  		




  44
  44
  
    
SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author


  		




  45
  45
  
    
FROM taxonlabel


  		




  46
  46
  
    
WHERE taxonlabel.source_id = (SELECT source_by_shortname('NY'))


  		












Also available in:  Unified diff