Public
Snippet $346 authored by fred.lpo07

mammals_list_with_taxref

mammals_list_with_taxref.sql
Raw
WITH mammif_taxref AS (
    SELECT
        *
    FROM
        referentiel.taxref
    WHERE
        cd_nom = cd_ref
        AND rang IN ('ES','SSES')
        AND fr = 'P'
        AND group2_inpn = 'Mammifères')
SELECT
    DISTINCT o.id_species,
    o.name_species,
    st.latin_spec,
    tx.cd_nom as cd_nom_taxref,
    tx.nom_vern as nomvern_taxref,
    st.classe1,
    st.classe2,
    (CASE WHEN 
	tx.cd_nom IS NULL THEN FALSE
	ELSE TRUE
	END)::boolean esp_vraie
FROM
    obs_mam o
    LEFT JOIN dico.statut_espece st ON st.id_species = o.id_species
    LEFT JOIN mammif_taxref tx ON st.latin_spec = tx.lb_nom;
Styling with Markdown is supported
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!