pggeodb.nancy.inra.fr/db_cefs - db_cefs on pggeodb.nancy.inra.fr

View: v_ani_gpsgsm

Schema

public

Owner

morellet

Descriptions

Vue contenant toutes les informations des individus équipés de GPS et de GSM (en dehors des locs) - si 2 captures la même année seule la première est conservée

Fields

Rules

Indices

There are no indices for table v_ani_gpsgsm

Definition

CREATE VIEW public.v_ani_gpsgsm (
   ani_id,
   ani_etiq,
   cap_bague,
   cap_annee_suivi,
   cap_date,
   ani_sexe,
   cap_age_classe,
   cap_poids,
   cap_lpa,
   sit_nom_court,
   teq_nom_court,
   eqt_id_usuel,
   mar_libelle,
   mod_libelle,
   eqa_date_debut,
   eqa_date_fin,
   ani_mortalite,
   ani_date_mort,
   ani_cause_mort,
   eqa_activite,
   eqa_probleme,
   eqa_date_fin_text,
   eqa_date_fin_arrondi,
   ani_date_mort_arrondi,
   ani_date_mort_text,
   ani_poids_mort,
   ani_poids_mort_na,
   ani_remarque,
   ani_mort_x,
   ani_mort_y,
   ani_inconnu,
   cap_faon,
   cap_age,
   cap_age_corrige,
   cap_circou,
   cap_etat_sante,
   cap_heure_lacher,
   sit_id)
AS
SELECT t_animal_ani.ani_id,
   t_animal_ani.ani_etiq,
   t_capture_cap.cap_bague,
   t_capture_cap.cap_annee_suivi,
   t_capture_cap.cap_date,
   t_animal_ani.ani_sexe,
   t_capture_cap.cap_age_classe,
   t_capture_cap.cap_poids,
   t_capture_cap.cap_lpa,
   tr_site_capture_sit.sit_nom_court,
   tr_type_equipement_teq.teq_nom_court,
   t_equipement_eqt.eqt_id_usuel,
   tr_eqtmarque_mar.mar_libelle,
   tr_eqtmodel_mod.mod_libelle,
   tj_equipement_animal_eqt_ani_eqa.eqa_date_debut,
   tj_equipement_animal_eqt_ani_eqa.eqa_date_fin,
   t_animal_ani.ani_mortalite,
   t_animal_ani.ani_date_mort,
   t_animal_ani.ani_cause_mort,
   tj_equipement_animal_eqt_ani_eqa.eqa_activite,
   tj_equipement_animal_eqt_ani_eqa.eqa_probleme,
   tj_equipement_animal_eqt_ani_eqa.eqa_date_fin_text,
   tj_equipement_animal_eqt_ani_eqa.eqa_date_fin_arrondi,
   t_animal_ani.ani_date_mort_arrondi,
   t_animal_ani.ani_date_mort_text,
   t_animal_ani.ani_poids_mort,
   t_animal_ani.ani_poids_mort_na,
   t_animal_ani.ani_remarque,
   t_animal_ani.ani_mort_x,
   t_animal_ani.ani_mort_y,
   t_animal_ani.ani_inconnu,
   t_capture_cap.cap_faon,
   t_capture_cap.cap_age,
   t_capture_cap.cap_age_corrige,
   t_capture_cap.cap_circou,
   t_capture_cap.cap_etat_sante,
   t_capture_cap.cap_heure_lacher,
   tr_site_capture_sit.sit_id
FROM t_animal_ani
    LEFT JOIN t_capture_cap ON t_capture_cap.cap_ani_id = t_animal_ani.ani_id
    LEFT JOIN tj_equipement_animal_eqt_ani_eqa ON
        tj_equipement_animal_eqt_ani_eqa.eqa_ani_id = t_capture_cap.cap_ani_id AND tj_equipement_animal_eqt_ani_eqa.eqa_annee_suivi = t_capture_cap.cap_annee_suivi
    LEFT JOIN t_equipement_eqt ON t_equipement_eqt.eqt_id =
        tj_equipement_animal_eqt_ani_eqa.eqa_eqt_id
    LEFT JOIN tr_type_equipement_teq ON tr_type_equipement_teq.teq_id =
        t_equipement_eqt.eqt_teq_id
    LEFT JOIN tr_site_capture_sit ON t_capture_cap.cap_sit_id =
        tr_site_capture_sit.sit_id
    LEFT JOIN tr_eqtmarque_mar ON t_equipement_eqt.eqt_mar_id = tr_eqtmarque_mar.mar_id
    LEFT JOIN tr_eqtmodel_mod ON t_equipement_eqt.eqt_mod_id = tr_eqtmodel_mod.mod_id
WHERE (tr_type_equipement_teq.teq_id = ANY (ARRAY[2, 3])) AND (t_capture_cap.cap_id IN (
   SELECT min(t_capture_cap_1.cap_id) AS min
   FROM t_capture_cap t_capture_cap_1
   GROUP BY t_capture_cap_1.cap_ani_id, t_capture_cap_1.cap_annee_suivi
   )) AND (tr_type_equipement_teq.teq_id = ANY (ARRAY[2, 3]))
ORDER BY t_capture_cap.cap_annee_suivi, t_capture_cap.cap_date, t_animal_ani.ani_etiq;

COMMENT ON VIEW public.v_ani_gpsgsm
IS 'Vue contenant toutes les informations des individus équipés de GPS et de GSM (en dehors des locs) - si 2 captures la même année seule la première est conservée';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23