Project

General

Profile

« Previous | Next » 

Revision 12188

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added view names using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW

View differences:

trunk/validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql
10 10 
-- ------------------
11 11 
-- 1. Count records
12 12 
-- ------------------
13 
CREATE OR REPLACE VIEW traits_01_count_records AS
13 14 
SELECT COUNT(*) AS totalrecords
14 15 
FROM "TraitObservation"
15 16 
WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
......
18 19 
-- ------------------
19 20 
-- 2. Count trait names
20 21 
-- ------------------
22 
CREATE OR REPLACE VIEW traits_02_count_trait_names AS
21 23 
SELECT COUNT(DISTINCT "measurementName") AS traits
22 24 
FROM "TraitObservation"
23 25 
WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
......
26 28 
-- ------------------
27 29 
-- 3. List trait names
28 30 
-- ------------------
31 
CREATE OR REPLACE VIEW traits_03_list_trait_names AS
29 32 
SELECT DISTINCT "measurementName" AS trait
30 33 
FROM "TraitObservation"
31 34 
WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
......
35 38 
-- ------------------
36 39 
-- 4. Count records per trait
37 40 
-- ------------------
41 
CREATE OR REPLACE VIEW traits_04_count_records_per_trait AS
38 42 
SELECT "measurementName" AS trait, COUNT(*) AS measurements
39 43 
FROM "TraitObservation"
40 44 
WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
......
45 49 
-- ------------------
46 50 
-- 5. Count taxa
47 51 
-- ------------------
52 
CREATE OR REPLACE VIEW traits_05_count_taxa AS
48 53 
SELECT COUNT(DISTINCT
49 54 
TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
50 55 
) AS taxa
......
55 60 
-- ------------------
56 61 
-- 6. List distinct taxa
57 62 
-- ------------------
63 
CREATE OR REPLACE VIEW traits_06_list_distinct_taxa AS
58 64 
SELECT DISTINCT
59 65 
TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
60 66 
AS taxonwithauthor
......
66 72 
-- ------------------
67 73 
-- 7. Trait, value and units
68 74 
-- ------------------
75 
CREATE OR REPLACE VIEW traits_07_trait_value_and_units AS
69 76 
SELECT "measurementName" AS trait, "measurementValue" AS value, "measurementUnit" AS units
70 77 
FROM "TraitObservation"
71 78 
WHERE "taxonName" IS NOT NULL AND "taxonName"<>'' AND "measurementName" IS NOT NULL AND "measurementName"<>''
......
75 82 
-- ------------------
76 83 
-- 8. "taxonName", trait and value for first 5000 records
77 84 
-- ------------------
85 
CREATE OR REPLACE VIEW traits_08_taxonname_trait_and_value_for_first_5000_records AS
78 86 
SELECT
79 87 
TRIM(CONCAT_WS(' ',IFNULL("taxonName",''),IFNULL("scientificNameAuthorship",'')))
80 88 
AS taxonwithauthor,

Also available in: Unified diff