Revision 13048
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql | ||
|---|---|---|
| 94 | 94 |
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 |
| 95 | 95 |
'; |
| 96 | 96 |
|
| 97 |
SET enable_mergejoin = off; |
|
| 98 |
SET enable_hashjoin = off; |
|
| 97 | 99 |
CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS |
| 100 |
SELECT |
|
| 101 |
(SELECT fullname FROM party WHERE party.party_id = collector_id) AS "collectorName" |
|
| 102 |
, collectionnumber AS "collectionNumber" |
|
| 103 |
, collectiondate AS "dateCollected" |
|
| 104 |
, count(*) AS "specimenRecords" |
|
| 105 |
FROM taxonoccurrence |
|
| 106 |
JOIN aggregateoccurrence USING (taxonoccurrence_id) |
|
| 107 |
JOIN plantobservation USING (aggregateoccurrence_id) |
|
| 108 |
JOIN specimenreplicate USING (plantobservation_id) |
|
| 109 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname('NY'))
|
|
| 110 |
GROUP BY "collectorName", "collectionNumber", "dateCollected" |
|
| 111 |
ORDER BY "collectorName", "collectionNumber", "dateCollected" |
|
| 98 | 112 |
; |
| 113 |
RESET enable_hashjoin; |
|
| 114 |
RESET enable_mergejoin; |
|
| 99 | 115 |
|
| 100 | 116 |
CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS |
| 101 | 117 |
SELECT |
Also available in: Unified diff
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_12_distinct_collector_name_collect_num_date_w_count