Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create view api_index as
- select
- CASE WHEN y.infra_rank is null THEN
- y.genus_name || ' ' || y.species_name
- ELSE
- trim(trailing from y.genus_name || ' ' || y.species_name || ' ' || y.infra_rank || ' ' || y.infra_name)
- END as scientific_name,
- not(is_synonym) as primary,
- class_name as class,
- order_name as order,
- family_name as family,
- y.genus_name as genus,
- y.species_name as species,
- y.authority as authority,
- y.infra_name,
- y.infra_rank,
- y.infra_authority,
- species.id as species_id,
- modified_year,
- categories.code as category,
- criteria
- from synonyms y
- join species
- on y.species_id = species.id
- join assessment_mappings
- on assessment_mappings.region_id = 0 and assessment_mappings.species_id = species.id
- join assessments
- on assessments.id = assessment_mappings.assessment_id
- join categories
- on assessments.category_id = categories.id
- where coalesce(y.species_name,'')!='';
Add Comment
Please, Sign In to add comment