/ - Diff - BIEN 3 - NCEAS Projects

     -- ------------------
     -- 1. Count of projects
     -- ------------------
     CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
     CREATE VIEW _plots_01_count_of_projects AS
     SELECT COUNT(*) AS projects
     FROM projects
+    ;
-...
     -- ------------------
     -- 2. List of project names
     -- ------------------
     CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
     CREATE VIEW _plots_02_list_of_project_names AS
     SELECT project_name AS projectname
     FROM projects
+    ;
-...
     -- ------------------
     -- 3. Count of all plots in this source
     -- ------------------
     CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
     CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS
     SELECT COUNT(DISTINCT "SiteCode") AS plots
     FROM projects p JOIN "plotMetadata" pm
     ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 4. Count of plots in each project in this source
     -- ------------------
     CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
     CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
     SELECT p.project_name, COUNT(*) AS plots
     FROM projects p JOIN "plotMetadata" pm
     ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 5.List of plot codes by project
     -- ------------------
     CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
     CREATE VIEW _plots_05_list_of_plot_codes_by_project AS
     SELECT p.project_name, pm."SiteCode"
     FROM projects p JOIN "plotMetadata" pm
     ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 6. List of plots with stem measurements
     -- ------------------
     CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
     CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS
     SELECT DISTINCT p.project_name, pm."SiteCode"
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 7.List of plots with counts of individuals per species
     -- ------------------
     CREATE OR REPLACE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
     CREATE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
     SELECT DISTINCT p.project_name, pm."SiteCode"
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 8.List of plots which use percent cover
     -- ------------------
     CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
     CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
     SELECT DISTINCT p.project_name, pm."SiteCode"
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id
-...
     -- ------------------
     -- 9.List of plots which use line-intercept
     -- ------------------
     CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
     CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
     SELECT DISTINCT p.project_name, pm."SiteCode"
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id
-...
     -- incorrect total for plots such as Gentry plot, which count individuals
     -- per species per subplot
     -- ------------------
     CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
     CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
     SELECT p.project_name, "SiteCode" AS plotcode,
     SUM("NoInd") AS individuals
     FROM projects p
-...
     --
     -- Method: count records in stems table
     -- ------------------
     CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
     CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
     SELECT p.project_name, "SiteCode" AS plotcode,
     COUNT(DISTINCT stem_id) AS stems
     FROM projects p
-...
     -- Note: Assumes identical concatenation of taxonomic fields
     --       to form verbatim taxon name in vegBIEN
     -- ------------------
     CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
     CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
     SELECT project_name, plotcode, COUNT(taxon) AS taxa
     FROM
+    (
-...
     -- Note: Assumes identical concatenation of taxonomic fields
     --       to form verbatim taxon name in vegBIEN.
     -- ------------------
     CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
     CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
     SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
     TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
     IF(infra_ep_1 IS NULL,IFNULL(auth,''),
-...
     -- Note: Assumes identical concatenation of taxonomic fields
     --       to form verbatim taxon name in vegBIEN
     -- ------------------
     CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
     CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
     SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
     TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
     IF(infra_ep_1 IS NULL,IFNULL(auth,''),
-...
     --       per species per plot) in same query.
     -- Note 3: currently, there are no SALVIAS percent cover plots in BIEN
     -- ------------------
     CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
     CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
     SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
     TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
     IF(infra_ep_1 IS NULL,IFNULL(auth,''),
-...
     --       stratum) with plots without subplots or strata (one cover measure
     --       per species per plot.
     -- ------------------
     CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
     CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
     SELECT DISTINCT p.project_name, "SiteCode" AS plotcode,
     TRIM(CONCAT_WS(' ',IFNULL("Family",''),IFNULL("Genus",''),IFNULL("Species",''),
     IF(infra_ep_1 IS NULL,IFNULL(auth,''),
-...
     -- ------------------
     -- 17. Count of subplots per plot, for each project
     -- ------------------
     CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
     CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
     SELECT project_name, pm."SiteCode" AS plotcode, COUNT(DISTINCT "Line") as subplots
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id
-...
     --
     -- Note: includes plots with and without subplots
     -- ------------------
     CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
     CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
     SELECT DISTINCT project_name, pm."SiteCode" AS plotcode, "Line" as subplot
     FROM projects p
     JOIN "plotMetadata" pm ON p.project_id=pm.project_id

     SET search_path TO "NY";
     CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
     CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
     SELECT COUNT(*) "totalSpecimenRecords"
     FROM "Ecatalog_all";
     COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db IS '
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS
     CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS
     SELECT COUNT(DISTINCT family) AS families FROM "Ecatalog_all" WHERE family IS NOT NULL;
     COMMENT ON VIEW _specimens_02_count_of_unique_verbatim_families IS '
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS
     CREATE VIEW _specimens_03_list_of_verbatim_families AS
     SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"
     FROM "Ecatalog_all"
     WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;
-...
     Check: should return same number of rows
     ';
     CREATE OR REPLACE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS
     CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS
     SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet")) AS subspecies FROM "Ecatalog_all"
     WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;
     COMMENT ON VIEW _specimens_04_count_of_unique_verbatim_species_without_author IS '
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS
     CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS
     SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS subspecies FROM "Ecatalog_all"
     WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;
     COMMENT ON VIEW _specimens_05_list_of_verbatim_species_excluding_author IS '
     Check: should return 3335 rows
     ';
     CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS
     CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS
     SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet", subspecies)) AS "specificEpithet" FROM "Ecatalog_all"
     WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;
     COMMENT ON VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author IS '
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS
     CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS
     SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"
     FROM "Ecatalog_all"
     WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;
-...
     Check: should return 40145 rows
     ';
     CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS
     CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS
     SELECT COUNT(DISTINCT TRIM(CONCAT_WS(' ',
     TRIM(CONCAT_WS(' ', genus, IFNULL("specificEpithet",''), IFNULL(subspecies,''))),
     TRIM(IFNULL("scientificNameAuthorship",''))
-...
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS
     CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS
     SELECT DISTINCT TRIM(CONCAT_WS(' ',
     TRIM(CONCAT_WS(' ', genus, IFNULL("specificEpithet",''), IFNULL(subspecies,''))),
     TRIM(IFNULL("scientificNameAuthorship",''))
-...
     Check: should return 45997 rows
     ';
     CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS
     CREATE VIEW _specimens_10_count_number_of_records_by_institution AS
     SELECT specimen_duplicate_institutions, COUNT(*) AS records
     FROM "Ecatalog_all"
     GROUP BY specimen_duplicate_institutions;
-...
     Check: should return 6 rows
     ';
     CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS
     CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS
     SELECT DISTINCT country, "stateProvince", county
     FROM "Ecatalog_all";
     COMMENT ON VIEW _specimens_11_list_of_three_standard_political_divisions IS '
-...
     Check: should return 5232 rows
     ';
     CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
     CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
     SELECT IFNULL("recordedBy",'') AS "collectorName", IFNULL("collectorNumber",'') AS "collectionNumber",
     CONCAT_WS('-', "yearCollected", "monthCollected", "dayCollected") AS "dateCollected", COUNT(*) AS "specimenRecords"
     FROM "Ecatalog_all"
-...
     Check: should return 309396 rows
     ';
     CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
     CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
     SELECT
+    (
     SELECT COUNT(*)
-...
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS
     CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS
     SELECT
+    (
     SELECT COUNT(*)
-...
     Check: should return 1 row
     ';
     CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS
     CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS
     SELECT DISTINCT locality__main AS "localityDescription"
     FROM "Ecatalog_all"
     WHERE locality__main IS NOT NULL;
-...
     Check: should return 125592 records
     ';
     CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS
     CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS
     SELECT "specimenDescription" AS "specimenDescription"
     FROM "Ecatalog_all"
     WHERE "specimenDescription" IS NOT NULL;

trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
9	9
10	10	SET search_path TO public_validations, public;
11	11
12		CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
	12	CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
13	13	;
14	14
15		CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS
	15	CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS
16	16	;
17	17
18		CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS
	18	CREATE VIEW _specimens_03_list_of_verbatim_families AS
19	19	;
20	20
21		CREATE OR REPLACE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS
	21	CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS
22	22	;
23	23
24		CREATE OR REPLACE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS
	24	CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS
25	25	;
26	26
27		CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS
	27	CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS
28	28	;
29	29
30		CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS
	30	CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS
31	31	;
32	32
33		CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS
	33	CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS
34	34	;
35	35
36		CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS
	36	CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS
37	37	;
38	38
39		CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS
	39	CREATE VIEW _specimens_10_count_number_of_records_by_institution AS
40	40	;
41	41
42		CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS
	42	CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS
43	43	;
44	44	COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db
45	45	IS '
46	46	Note: character set issues may cause mis-matches. This query is a good way to reveal character set issues, either in source db or in BIEN
47	47	';
48	48
49		CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
	49	CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
50	50	;
51	51
52		CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
	52	CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
53	53	;
54	54
55		CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS
	55	CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS
56	56	;
57	57
58		CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS
	58	CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS
59	59	;
60	60
61		CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS
	61	CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS
62	62	;

     -- ------------------
     -- 1. Count records
     -- ------------------
     CREATE OR REPLACE VIEW _traits_01_count_records AS
     CREATE VIEW _traits_01_count_records AS
     SELECT COUNT(*) AS "totalRecords"
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
-...
     -- ------------------
     -- 2. Count trait names
     -- ------------------
     CREATE OR REPLACE VIEW _traits_02_count_trait_names AS
     CREATE VIEW _traits_02_count_trait_names AS
     SELECT COUNT(DISTINCT name) AS traits
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
-...
     -- ------------------
     -- 3. List trait names
     -- ------------------
     CREATE OR REPLACE VIEW _traits_03_list_trait_names AS
     CREATE VIEW _traits_03_list_trait_names AS
     SELECT DISTINCT name AS trait
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
-...
     -- ------------------
     -- 4. Count records per trait
     -- ------------------
     CREATE OR REPLACE VIEW _traits_04_count_records_per_trait AS
     CREATE VIEW _traits_04_count_records_per_trait AS
     SELECT name AS trait, COUNT(*) AS measurements
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
-...
     -- Note: No morphospecies in trait table, therefore count
     -- 		taxon + authority only
     -- ------------------
     CREATE OR REPLACE VIEW _traits_05_count_taxa AS
     CREATE VIEW _traits_05_count_taxa AS
     SELECT COUNT(DISTINCT (taxonname, author)) AS taxa
     FROM taxonverbatim
     WHERE source_id = (SELECT source_by_shortname(current_schema))
-...
     -- Note 2: Note formation of taxonCorrected: includes family ONLY if
     -- taxon is not determined at least to genus
     -- ------------------
     CREATE OR REPLACE VIEW _traits_06_list_taxa AS
     CREATE VIEW _traits_06_list_taxa AS
     SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor
     FROM taxonverbatim
     WHERE source_id = (SELECT source_by_shortname(current_schema))
-...
     -- ------------------
     -- 7. Trait, value and units for first 5000 records
     -- ------------------
     CREATE OR REPLACE VIEW _traits_07_trait_value_and_units_for_first_5000_records AS
     CREATE VIEW _traits_07_trait_value_and_units_for_first_5000_records AS
     SELECT name AS trait, value, units
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
-...
     -- ------------------
     -- 8. Taxon, trait and value for first 5000 records
     -- ------------------
     CREATE OR REPLACE VIEW _traits_08_taxon_trait_and_value_for_first_5000_records AS
     CREATE VIEW _traits_08_taxon_trait_and_value_for_first_5000_records AS
     SELECT concat_ws(' ', taxonname, author) AS taxonwithauthor, name AS trait, value
     FROM taxonverbatim
     JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent

     -- ------------------
     -- 1. Count records
     -- ------------------
     CREATE OR REPLACE VIEW _traits_01_count_records AS
     CREATE VIEW _traits_01_count_records AS
     SELECT COUNT(*) AS totalrecords
     FROM "TraitObservation"
     WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
-...
     -- ------------------
     -- 2. Count trait names
     -- ------------------
     CREATE OR REPLACE VIEW _traits_02_count_trait_names AS
     CREATE VIEW _traits_02_count_trait_names AS
     SELECT COUNT(DISTINCT "measurementName") AS traits
     FROM "TraitObservation"
     WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
-...
     -- ------------------
     -- 3. List trait names
     -- ------------------
     CREATE OR REPLACE VIEW _traits_03_list_trait_names AS
     CREATE VIEW _traits_03_list_trait_names AS
     SELECT DISTINCT "measurementName" AS trait
     FROM "TraitObservation"
     WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
-...
     -- ------------------
     -- 4. Count records per trait
     -- ------------------
     CREATE OR REPLACE VIEW _traits_04_count_records_per_trait AS
     CREATE VIEW _traits_04_count_records_per_trait AS
     SELECT "measurementName" AS trait, COUNT(*) AS measurements
     FROM "TraitObservation"
     WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
-...
     -- ------------------
     -- 5. Count taxa
     -- ------------------
     CREATE OR REPLACE VIEW _traits_05_count_taxa AS
     CREATE VIEW _traits_05_count_taxa AS
     SELECT COUNT(DISTINCT
     TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
     ) AS taxa
-...
     -- ------------------
     -- 6. List distinct taxa
     -- ------------------
     CREATE OR REPLACE VIEW _traits_06_list_distinct_taxa AS
     CREATE VIEW _traits_06_list_distinct_taxa AS
     SELECT DISTINCT
     TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
     AS taxonwithauthor
-...
     -- ------------------
     -- 7. Trait, value and units
     -- ------------------
     CREATE OR REPLACE VIEW _traits_07_trait_value_and_units AS
     CREATE VIEW _traits_07_trait_value_and_units AS
     SELECT "measurementName" AS trait, "measurementValue" AS value, "measurementUnit" AS units
     FROM "TraitObservation"
     WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
-...
     -- ------------------
     -- 8. "taxonName", trait and value for first 5000 records
     -- ------------------
     CREATE OR REPLACE VIEW _traits_08_taxonname_trait_and_value_for_first_5000_records AS
     CREATE VIEW _traits_08_taxonname_trait_and_value_for_first_5000_records AS
     SELECT
     TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
     AS taxonwithauthor,

Project

General

Profile

Revision 12923

Added by Aaron Marcuse-Kubitza almost 11 years ago