Extraction depuis SaS ou R

Quelque soit le logiciel statistique que vous utilisez, quelques points avant de commencer :

1- Connaitre le Modèle de Données : voir le MCD dans la forge ici : https://forge-dga.jouy.inra.fr/projects/sivol/wiki/Mod%C3%A8le_conceptuel

2- Connaitre un minimum le SQL (Structured Query Langage)

3- Se poser les bonnes questions avant d'écrire son SQL. En général ne pas partir de la table ANIMAL. Mettre d'abord la table de mesure (PESEE, ABATTAGE, .etc.) ou la table liée à la repro (IA, LUTTE_NAT,MISEBAS) avant la table ANIMAL.

4- Même si vous connaissez SaS ou R, ne pas lire toutes les données d'une espèce et faire vos calculs dans le logiciel de statistiques. Avec une bonne interrogation, vous pouvez aller chercher des infos de plusieurs tables à la fois.

5- Bien jouer le jeu de n'extraire que les données de vos UE favorites.

6- Pour les anciens utilisateurs :
-Ne pas travailler sur le champ Boucle de la table PRESENT. Il n'est pas forcément UNIQUE.
-Les jointures externes avec : A.id_animal=B.id_animal(signe plus/+) AND A.elv_ne=B.elv_ne(signe plus/+) and A.espece=.espece(signe plus/+) N'est PAS recommandé et ne sera plus valable en PostgreSQL
-Lire les champs d_etat_repro et cd_etat_repro dans la table ANIMAL et dans la table REPRO pour avoir le dernier état lié à la reproduction (A=Avortée,V=Vide,G=Gestante,M=Mise-bas,S=Saillie,V=Vide)

1-Extraction via SaS au CTIG

Fichier configuration

Il faut avoir la variable ORACLE_HOME comme ceci.
Faire env
ORACLE_HOME=/opt/ora11g/app/ora11g/product/11.2.0/client_1

Je ne sais plus où il faut le mettre sous AIx c'était dans le .profile mais maintenant sous linux, I don't know

Appel depuis SaS

proc sql;
connect to oracle
(user=sasora password=sasora path='@dga5E');
create table repro as
SELECT elv_ne,id_animal,sexe,campagne,saison,input(d_debut_saison,date9.) as d_debut_saison,lot_lutte
FROM connection to oracle
(SELECT elv_ne,id_animal,sexe,campagne,saison,substr(d_debut_saison,1,9) as d_debut_saison,lot_lutte
FROM GEEDOC.repro
WHERE elv_prod='12232099' AND prod='1' AND espece='2' AND campagne='2020' AND saison='1'
ORDER BY id_animal,elv_ne);
disconnect from oracle;
quit;

proc print;
run;

user=identifiant utilisateur Oracle
password=mot de passe
path='@dga5E' : base de données GEEDOC

Recommandations :

1- Mettre dans la table/tableau SaS autant de colonnes que dans le Select Oracle

2- Traitement spécial pour les dates pour les avoir au format date dans SaS.
Dans Oracle substr(d_debut_saison,1,9) et dans le tableau SaS input(d_debut_saison,date9.) as d_debut_saison

3-Dans cet exemple : l'identifiant animal est elv_ne+id_animal . Si vous voulez le no IPG, il faudra faire une jointure avec la table ANIMAL.

Exemple avec une jointure :

proc sql;
connect to oracle
(user=sasora password=sasora path='@dga5E');
create table repro as
SELECT ipg, elv_ne,id_animal,sexe,campagne,saison,input(d_debut_saison,date9.) as d_debut_saison,lot_lutte,typ_gen,famille,lignee
FROM connection to oracle
(SELECT A.ipg,R.elv_ne,R.id_animal,R.sexe,R.campagne,R.saison,
substr(R.d_debut_saison,1,9) as d_debut_saison,R.lot_lutte,A.typ_gen,A.famille,A.lignee
FROM GEEDOC.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece

WHERE R.elv_prod='12232099' AND R.prod='1' AND R.espece='2'
AND R.campagne='2020' AND R.saison='1'
ORDER BY A.ipg);
disconnect from oracle;
quit;

proc print;
run;

Pour les spectres MIR : il faut ajouter DBMAX_TEXT=32767 dans la connexion

proc sql;
connect to oracle
(user=sasora password=sasora path='@dga5E' DBMAX_TEXT=32767);
Faire un exemple

disconnect from oracle;
quit;

proc print;
run;

2-Extraction via R au CTIG

Procédure donnée par Hervé Chapuis pour Sivol adaptée pour Geedoc.

Fichier de configuration

Il faut ajouter un fichier .Renviron sous son HOME_DIRECTORY, ce fichier contenant la variable ORACLE_HOME :

hchapuis@dga20:~# vi .Renviron
donne :
ORACLE_HOME=/opt/ora11g/app/ora11g/product/11.2.0/client_1
export ORACLE_HOME

Appel depuis R

Sur dga20, on peut lancer R depuis un terminal, ce qui ouvre un mode « ligne de commande ».
On peut également utiliser l’interface graphique Rstudio, dont il existe 2 versions :
*Une version accessible depuis le menu Applications > Programmation. Version de R = 3.3.1
*Une version accessible en ligne, en ouvrant un navigateur internet depuis son PC. C’est Rstudio Server (version de R = 3.5.0). J’utilise Firefox. L’adresse est http://dga20.jouy.inra.fr:8787 Quand on n’est pas connecté depuis un centre INRA, le VPN Global Protect est requis.

Depuis la fenêtre de Rstudio Server (via Firefox) les étapes sont :
1. Appel d’un package pour attaquer la base. Il y en existe plusieurs (ROracle, odbc, DBI,…) library(DBI)

2. Etablissement de la connexion à la base.
channel<-dbConnect(odbc::odbc(), UID ="XXX", PWD = XXX", "DGA5E")
XXX contient l’identifiant et le mot de passe donnant accès à GEEDOC. DGA5E est le nom de la base de données GEEDOC.

3. Formulation d’une requête SQL.
dbGetQuery(channel,'select * from (select distinct d_pesee from geedoc.PESEE WHERE elv_prod='12232099' AND espece='2' AND prod='1' AND d_pesee>'01-JAN-2020' AND nat_pesee='2') WHERE rownum <= 10')
qui va donner les 10 premières dates de pesées de la table PESEE.
La réponse est : (non testé je ne sais pas si les dates apparaissent avec format là)
D_PESEE
1 09-JAN-2020
2 20-JAN-2020
3 29-JAN-2020
4 30-JAN-2020
5 31-JAN-2020
6 13-FEB-2020
7 21-FEB-2020
8 26-FEB-2020
9 06-MAR-2020

ATTENTION !! Pour que cela fonctionne, le fichier de configuration .Renviron est indispensable.

3-Aide au SQL

Vous avez de nombreux documents sur le langage SQL.
Je ne donne pas ici un cours de SQL mais je donne quelques exemples d'extraction liés à la base Geedoc.
Attention, je n'ai pas tout testé.

Au niveau des mesures

Pour sélectionner les pesées de croissance entre 2 dates

SELECT A.ipg,P.elv_ne,P.id_animal,substr(P.d_pesee,1,9) as d_pesee,P.lot_pesee,P.cd_pesee,P.poids,A.typ_gen,A.famille,A.lignee
FROM geedoc.pesee P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1' AND P.nat_pesee='2'
AND P.d_pesee between '01-JAN-2020' AND '01-MAY-2020'
ORDER BY A.ipg;

Pour avoir les pesées ET les Nec à la même date connue (testé)

SELECT A.ipg,P.elv_ne,P.id_animal,substr(P.d_pesee,1,9) as d_pesee,P.lot_pesee,P.cd_pesee,P.poids,E.note_lomb,E.note_ster,E.cd_etat
FROM geedoc.pesee P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece
LEFT JOIN geedoc.etat_corporel E
ON P.id_animal=E.id_animal AND P.elv_ne=E.elv_ne AND P.espece=E.espece and P.d_pesee=E.d_note_etat

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='2' AND P.nat_pesee!='1' AND P.d_pesee ='20-FEB-2020'
ORDER BY A.ipg;

Pour avoir les pesées liées aux femelles intra campagne/saison entre le début de saison et le début +250 jours (testé)

SELECT R.elv_ne,R.id_animal,A.ipg,P.d_pesee,P.h_pesee,P.d_pesee-A.d_nais as age,P.lot_pesee,P.cd_pesee, P.poids
// Je pars de la table Repro
FROM geedoc.repro R
// Jointure avec la table Animal pour avoir le N° IPG et la date de naissance
LEFT JOIN geedoc.Animal A ON R.id_animal=A.id_animal and R.elv_ne=A.elv_ne and R.espece=A.espece
// Jointure avec la table Pesee
LEFT JOIN geedoc.pesee P
ON R.id_animal=P.id_animal and R.elv_ne=P.elv_ne and R.espece=P.espece and R.elv_prod=P.elv_prod and R.prod=P.prod

// Conditions
Where R.elv_prod='12232099' and R.prod='1' and R.espece='2'
and R.campagne='2014' and R.saison='1' and R.sexe='2' and P.nat_pesee = '3'
and P.d_pesee >=R.d_debut_saison and P.d_pesee <= R.d_debut_saison+250;

Au niveau des animaux

Pour sélectionner tous les animaux nés d'une campagne/saison (testé)

SELECT A.ipg,P.elv_ne,P.id_animal,substr(A.d_nais,1,9) as d_nais,A.sexe,A.typ_gen,A.famille,A.lignee,A.pg_elv,A.pg_id_animal,A.mg_elv,A.mg_id_animal
FROM GEEDOC.present P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1'
AND ((A.mp_id_animal IS NULL AND A.mg_campagne='2020' AND A.mg_saison='1') OR (A.mp_id_animal IS NOT NULL AND A.mp_campagne='2020' AND A.mp_saison='1'))
ORDER BY A.ipg;

Comme il n'y a pas de Transfert d'Embryon et donc pas de mère porteuse, vous pouvez simplifier (testé)

SELECT A.ipg,P.elv_ne,P.id_animal,substr(A.d_nais,1,9) as d_nais,A.sexe,A.typ_gen,A.famille,A.lignee,A.pg_elv,A.pg_id_animal,A.mg_elv,A.mg_id_animal
FROM GEEDOC.present P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1'
AND A.mg_campagne='2020' AND A.mg_saison='1'
ORDER BY A.ipg;

Pour afficher en plus le no IPG du père pour ces animaux (testé)

SELECT A.ipg,P.elv_ne,P.id_animal,substr(A.d_nais,1,9) as d_nais,A.sexe,A.typ_gen,A.famille,A.lignee,
PG.IPG as pg_ipg,A.pg_elv,A.pg_id_animal,PG.typ_gen as pg_typ_gen, PG.famille as pg_famille,PG.lignee as pg_lignee
FROM GEEDOC.present P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece
LEFT JOIN geedoc.animal PG
ON A.pg_id_animal=PG.id_animal AND A.pg_elv=PG.elv_ne AND A.espece=PG.espece

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1'
AND A.mg_campagne='2020' AND A.mg_saison='1'
ORDER BY A.ipg;

Si vous testez avec 'INNER JOIN geedoc.animal PG' pour avoir le père, vous verrez que vous n'aurez que les animaux avec un père connu.

Pour afficher le no IPG du Père ET le no IPG de la Mère pour ces animaux (testé)

SELECT A.ipg,P.elv_ne,P.id_animal,substr(A.d_nais,1,9) as d_nais,A.sexe,A.typ_gen,A.famille,A.lignee,
PG.IPG as pg_ipg,A.pg_elv,A.pg_id_animal,PG.typ_gen as pg_typ_gen, PG.famille as pg_famille,PG.lignee as pg_lignee,
MG.IPG as mg_ipg,A.mg_elv,A.mg_id_animal,MG.typ_gen as mg_typ_gen, MG.famille as mg_famille,MG.lignee as mg_lignee
FROM geedoc.present P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece
LEFT JOIN geedoc.animal PG
ON A.pg_id_animal=PG.id_animal AND A.pg_elv=PG.elv_ne AND A.espece=PG.espece
LEFT JOIN geedoc.animal MG
ON A.mg_id_animal=MG.id_animal AND A.mg_elv=MG.elv_ne AND A.espece=MG.espece

WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1'
AND A.mg_campagne='2020' AND A.mg_saison='1'
ORDER BY A.ipg;

Si on veut afficher les grands parents
ggp =grand père paternel
gmp =grand mère paternelle
gpm =grand père maternel
mmm =grand mère maternelle

SELECT A.ipg,P.elv_ne,P.id_animal,substr(A.d_nais,1,9) as d_nais,A.sexe,A.typ_gen,A.famille,A.lignee,
PG.IPG as pg_ipg,A.pg_elv,A.pg_id_animal,PG.typ_gen as pg_typ_gen, PG.famille as pg_famille,PG.lignee as pg_lignee,
MG.IPG as mg_ipg,A.mg_elv,A.mg_id_animal,MG.typ_gen as mg_typ_gen, MG.famille as mg_famille,MG.lignee as mg_lignee,
PG.pg_elv as gpp_elv,PG.pg_id_animal as gpp_id_animal, PG.mg_elv as gmp_elv,PG.mg_id_animal as gmp_id_animal,
MG.pg_elv as gpm_elv,MG.pg_id_animal as gpm_id_animal, MG.mg_elv as gmm_elv,MG.mg_id_animal as gmm_id_animal

FROM geedoc.present P
LEFT JOIN geedoc.animal A
ON P.id_animal=A.id_animal AND P.elv_ne=A.elv_ne AND P.espece=A.espece
LEFT JOIN geedoc.animal PG
ON A.pg_id_animal=PG.id_animal AND A.pg_elv=PG.elv_ne AND A.espece=PG.espece
LEFT JOIN geedoc.animal MG
ON A.mg_id_animal=MG.id_animal AND A.mg_elv=MG.elv_ne AND A.espece=MG.espece
WHERE P.elv_prod='12232099' AND P.espece='2' AND P.prod='1'
AND A.mg_campagne='2020' AND A.mg_saison='1'
ORDER BY A.ipg;

Si on veut l'IPG des grands parents : il faut refaire une auto-jointure sur la table animal et ceci 4 fois.

Auto-jointure pour avoir le Grand Père Paternel

LEFT JOIN geedoc.animal GPP ON PG.pg_id_animal=GPP.id_animal AND PG.pg_elv=GPP.elv_ne AND PG.espece=GPP.espece

Auto-jointure pour avoir la Grand Mère Paternelle

LEFT JOIN geedoc.animal GMP ON PG.mg_id_animal=GMP.id_animal AND PG.mg_elv=GMP.elv_ne AND PG.espece=GMP.espece

Auto-jointure pour avoir le Grand Père Maternel

LEFT JOIN geedoc.animal GPM ON MG.pg_id_animal=GPM.id_animal AND MG.pg_elv=GPM.elv_ne AND MG.espece=GPM.espece

Auto-jointure pour avoir la Grand Mère Maternelle

LEFT JOIN geedoc.animal GMM ON MG.mg_id_animal=GMM.id_animal AND MG.mg_elv=GMM.elv_ne AND MG.espece=GMM.espece

Au niveau des génotypes

Sélectionner des animaux pour le génotype PRP (testé)
G1.allele1||'/'|| G1.allele2 permet d'afficher le génotype PRP comme ceci IRRQP/IRRQS

SELECT P.elv_ne,P.id_animal,A.ipg,G1.allele1||'/'|| G1.allele2 as PRP
FROM geedoc.present P
LEFT JOIN geedoc.ANIMAL A ON P.id_animal=A.id_animal and P.elv_ne=A.elv_ne and P.espece=A.espece
LEFT JOIN (SELECT elv_ne,id_animal,espece,allele1,allele2
FROM geedoc.genotypes
WHERE genotype='PRP' and espece='1') G1
ON P.id_animal=G1.id_animal AND P.elv_ne=G1.elv_ne AND P.espece=G1.espece

WHERE P.elv_prod='18018900' and P.prod='1' and P.espece='1' AND substr(P.id_animal,1,2)='15'

Sélectionner des animaux avec 2 génotypes PRP et CaséineAlphaS1 (CaseineAS1) (testé)
trim(G2.allele1) permet d'enlever les blancs
trim(G2.allele1)||'/'||trim(G2.allele2) permet d'afficher le génotype CaseineAS1 comme ceci Ab/B34

SELECT P.elv_ne,P.id_animal,A.ipg,G1.allele1||'/'|| G1.allele2 as PRP, 
trim(G2.allele1)||'/'||trim(G2.allele2) as CaseineAS1
FROM geedoc.present P
LEFT JOIN geedoc.animal A ON P.id_animal=A.id_animal and P.elv_ne=A.elv_ne and P.espece=A.espece
LEFT JOIN (SELECT elv_ne,id_animal,espece,allele1,allele2
FROM geedoc.genotypes
WHERE genotype='PRP' and espece='1') G1
ON P.id_animal=G1.id_animal AND P.elv_ne=G1.elv_ne AND P.espece=G1.espece
LEFT JOIN (SELECT elv_ne,id_animal,espece,allele1,allele2
FROM geedoc.genotypes
WHERE genotype='CaseineAS1' and espece='1') G2
ON P.id_animal=G2.id_animal AND P.elv_ne=G2.elv_ne AND P.espece=G2.espece

WHERE P.elv_prod='18018900' and P.prod='1' and P.espece='1' AND substr(P.id_animal,1,2)='15'

Au niveau des Repro

Sélectionner tous les animaux d'une campagne/saison

SELECT R.elv_ne,R.id_animal,A.ipg,R.d_debut_saison,R.lot_lutte,R.sexe,A.typ_gen,A.famille,A.lignee
FROM geedoc.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece
WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2020' AND R.saison='1';

Sélectionner toutes les femelles d'une campagne/saison

SELECT R.elv_ne,R.id_animal,A.ipg,R.d_debut_saison,R.lot_lutte,R.sexe,A.typ_gen,A.famille,A.lignee
FROM geedoc.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece
WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2020' AND R.saison='1' AND R.sexe='2';

Sélectionner toutes les femelles d'un lot de lutte pour une campagne/saison

SELECT R.elv_ne,R.id_animal,A.ipg,R.d_debut_saison,R.lot_lutte,R.sexe,A.typ_gen,A.famille,A.lignee
FROM geedoc.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece
WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2020' AND R.saison='1' AND R.sexe='2' AND R.lot_lutte='10';

Sélectionner toutes les femelles actives à une date donnée d'un lot de lutte d'une campagne/saison

SELECT R.elv_ne,R.id_animal,A.ipg,R.d_debut_saison,R.lot_lutte,R.sexe,A.typ_gen,A.famille,A.lignee
FROM geedoc.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece
WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2020' AND R.saison='1' AND R.sexe='2' AND R.lot_lutte='10'
AND ((A.d_fact IS NULL) OR (A.d_fact IS NOT NULL AND A.d_fact < '22-JAN-2020'));

Sélectionner toutes les femelles de plusieurs lots de lutte d'une campagne/saison

SELECT R.elv_ne,R.id_animal,A.ipg,R.d_debut_saison,R.lot_lutte,R.sexe,A.typ_gen,A.famille,A.lignee
FROM geedoc.repro R
LEFT JOIN geedoc.animal A
ON R.id_animal=A.id_animal AND R.elv_ne=A.elv_ne AND R.espece=A.espece
WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2020' AND R.saison='1' AND R.sexe='2' AND R.lot_lutte IN ('10','20','30') ;

Sélectionner toutes les femelles avec le poids à la lutte (cd_pesee='10') (testé)

Normalement, il n'y a qu'une pesée de lutte (cd_pesee='10') intra campagne/saison.

SELECT R.elv_ne,R.id_animal,R.lot_Lutte,R.groupe,PL.d_pesee as d_lutte, PL.poids as pds_lutte
FROM geedoc.repro R
LEFT JOIN (SELECT elv_ne,id_animal,espece,campagne,saison,d_pesee,poids
FROM geedoc.pesee
WHERE elv_prod='12232099' AND espece='2' AND prod='1'
AND campagne='2019' AND saison ='1' AND cd_pesee ='10') PL
ON PL.id_animal=R.id_animal AND PL.elv_ne=R.elv_ne AND PL.espece=R.espece AND PL.campagne=R.campagne AND PL.saison=R.saison

WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1' AND R.campagne='2019' AND R.saison='1' AND R.sexe='2';

Sélectionner toutes les femelles avec le poids à la lutte (cd_pesee='10') ET le poids à la mise bas (cd_pesee='11') (testé)

Normalement, il n'y a qu'une pesée de mise bas (cd_pesee='11') intra campagne/saison.

SELECT R.elv_ne,R.id_animal,R.lot_Lutte,R.groupe,
PL.d_pesee as d_lutte, PL.poids as pds_lutte,PM.d_pesee as d_pds_mb,PM.poids as pds_mb
FROM geedoc.repro R
LEFT JOIN (SELECT elv_ne,id_animal,espece,campagne,saison,d_pesee,poids
FROM geedoc.pesee
WHERE elv_prod='12232099' AND espece='2' AND prod='1'
AND campagne='2019' AND saison ='1' AND cd_pesee ='10') PL
ON PL.id_animal=R.id_animal AND PL.elv_ne=R.elv_ne AND PL.espece=R.espece
AND PL.campagne=R.campagne AND PL.saison=R.saison
LEFT JOIN (SELECT elv_ne,id_animal,espece,campagne,saison,d_pesee,poids,cd_pesee
FROM geedoc.pesee
WHERE elv_prod='12232099' AND espece='2' AND prod='1'
AND campagne='2019' AND saison='1' AND cd_pesee ='11') PM
ON PM.id_animal=R.id_animal and PM.elv_ne=R.elv_ne and PM.espece=R.espece
AND PM.campagne=R.campagne AND PM.saison=R.saison

WHERE R.elv_prod='12232099' AND R.espece='2' AND R.prod='1'
AND R.campagne='2019' AND R.saison='1' AND R.sexe='2';

Au niveau des saillies

Pour avoir les IA fécondantes intra campagne/saison (testé) :

SELECT I.elv_ne,I.id_animal,MG.ipg,substr(S.d_saillie,1,9) as d_saillie,S.mod_saillie,S.utilise as fec,
I.pg_elv,I.pg_id_animal,PG.ipg as pg_ipg,substr(I.d_prelev,1,9) as d_prelev
FROM geedoc.ia I
LEFT JOIN geedoc.saillie S
ON I.id_animal=S.id_animal AND I.elv_ne=S.elv_ne AND I.d_saillie=S.d_saillie AND I.espece=S.espece

LEFT JOIN geedoc.animal MG
ON I.id_animal=MG.id_animal AND I.elv_ne=MG.elv_ne AND I.espece=MG.espece
LEFT JOIN geedoc.animal PG
ON I.pg_id_animal=PG.id_animal AND I.pg_elv=PG.elv_ne AND I.espece=PG.espece
WHERE I.elv_prod='12232099' AND I.espece='2' AND I.prod='1'
AND I.campagne='2020' AND I.saison='1'
AND S.utilise='F' AND S.mod_saillie='2';

Pour avoir les Luttes fécondantes intra campagne/saison (testé) :

SELECT L.elv_ne,L.id_animal,MG.ipg,substr(S.d_saillie,1,9) as d_saillie,S.mod_saillie,S.utilise as fec,
L.pg_elv,L.pg_id_animal,PG.ipg as pg_ipg,substr(L.d_fin_lutte,1,9) as d_fin
FROM geedoc.lutte_nat L
LEFT JOIN geedoc.saillie S
ON L.id_animal=S.id_animal AND L.elv_ne=S.elv_ne AND L.d_saillie=S.d_saillie AND L.espece=S.espece

LEFT JOIN geedoc.animal MG
ON L.id_animal=MG.id_animal AND L.elv_ne=MG.elv_ne AND L.espece=MG.espece
LEFT JOIN geedoc.animal PG
ON L.pg_id_animal=PG.id_animal AND L.pg_elv=PG.elv_ne AND L.espece=PG.espece
WHERE L.elv_prod='12232099' AND L.espece='2' AND L.prod='1'
AND L.campagne='2020' AND L.saison='1'
AND S.utilise='F' AND S.mod_saillie='1';

Pour avoir les IA fécondantes ET les luttes fécondantes intra campagne/saison (testé) :

SELECT I.elv_ne,I.id_animal,MG.ipg,substr(I.d_saillie,1,9) as d_saillie,S.mod_saillie,S.utilise as fec,
I.pg_elv,I.pg_id_animal,PG.ipg as pg_ipg,substr(I.d_prelev,1,9) as d_fin
FROM geedoc.ia I
LEFT JOIN geedoc.saillie S
ON I.id_animal=S.id_animal AND I.elv_ne=S.elv_ne AND I.d_saillie=S.d_saillie AND I.espece=S.espece

LEFT JOIN geedoc.animal MG
ON I.id_animal=MG.id_animal AND I.elv_ne=MG.elv_ne AND I.espece=MG.espece
LEFT JOIN geedoc.animal PG
ON I.pg_id_animal=PG.id_animal AND I.pg_elv=PG.elv_ne AND I.espece=PG.espece
WHERE I.elv_prod='12232099' AND I.espece='2' AND I.prod='1'
AND I.campagne='2020' AND I.saison='1' AND S.utilise='F' AND S.mod_saillie='2'
UNION
SELECT L.elv_ne,L.id_animal,MG.ipg,substr(L.d_saillie,1,9) as d_saillie,S.mod_saillie,S.utilise as fec,
L.pg_elv,L.pg_id_animal,PG.ipg as pg_ipg,substr(L.d_fin_lutte,1,9) as d_fin
FROM geedoc.lutte_nat L
LEFT JOIN geedoc.saillie S
ON L.id_animal=S.id_animal AND L.elv_ne=S.elv_ne AND L.d_saillie=S.d_saillie AND L.espece=S.espece

LEFT JOIN geedoc.animal MG
ON L.id_animal=MG.id_animal AND L.elv_ne=MG.elv_ne AND L.espece=MG.espece
LEFT JOIN geedoc.animal PG
ON L.pg_id_animal=PG.id_animal AND L.pg_elv=PG.elv_ne AND L.espece=PG.espece
WHERE L.elv_prod='12232099' AND L.espece='2' AND L.prod='1'
AND L.campagne='2020' AND L.saison='1' AND S.utilise='F' AND S.mod_saillie='1';

Au niveau des misebas

Sélectionner toutes les misebas d'une campagne/saison (testé)

SELECT M.elv_ne,M.id_animal,A.ipg,M.d_misebas,M.mod_mb,M.portee,M.no_lact,M.comment_mb,A.typ_gen,A.famille,A.lignee
FROM geedoc.misebas M
LEFT JOIN geedoc.animal A
ON M.id_animal=M.id_animal AND M.elv_ne=A.elv_ne AND M.espece=M.espece
WHERE M.elv_prod='12232099' AND M.espece='2' AND M.prod='1'
AND M.campagne='2020' AND M.saison='1';

Sélectionner toutes les misebas avec portée supérieure à zéro d'une campagne/saison (testé)

SELECT M.elv_ne,M.id_animal,A.ipg,M.d_misebas,M.mod_mb,M.portee,M.no_lact,M.comment_mb,A.typ_gen,A.famille,A.lignee
FROM geedoc.misebas M
LEFT JOIN geedoc.animal A ON M.id_animal=M.id_animal AND M.elv_ne=A.elv_ne AND M.espece=M.espece
WHERE M.elv_prod='12232099' AND M.espece='2' AND M.prod='1'
AND M.campagne='2020' AND M.saison='1' AND M.portee>0;

Sélectionner toutes les misebas d'une campagne/saison avec le poids à la misebas (testé)

Ne pas mettre M.d_misebas=P.d_pesee car la date de pesée n'est pas toujours la date de la misebas.

SELECT M.elv_ne,M.id_animal,M.d_misebas,M.mod_mb,M.portee,M.no_lact,PM.d_pesee,PM.poids
FROM geedoc.misebas M
LEFT JOIN (SELECT elv_ne,id_animal,espece,campagne,saison,d_pesee,poids,cd_pesee
FROM geedoc.pesee
WHERE elv_prod='12232099' AND espece='2' AND prod='1'
AND campagne='2020' AND saison='1' AND cd_pesee ='11') PM
ON PM.id_animal=M.id_animal AND PM.elv_ne=M.elv_ne AND PM.espece=M.espece
AND PM.campagne=M.campagne AND PM.saison=M.saison

WHERE M.elv_prod='12232099' AND M.espece='2' AND M.prod='1' AND M.campagne='2020' AND M.saison='1';

Au niveau des traites

Pour avoir toutes les traites à une date et un moment donné (testé)

SELECT  T.elv_ne,T.id_animal,A.ipg,substr(T.d_traite,1,9) as d_traite,T.moment,
T.lot_traite,T.place,T.qte_lait,T.ano_lait,T.tps_traite,h_fin_traite,T.debit_pas,tps_late,T.no_ech,T.tb,T.tp,T.ccs,T.ano_labo
FROM geedoc.traite_anim T
LEFT JOIN geedoc.animal A
ON T.id_animal=A.id_animal AND T.elv_ne=A.elv_ne AND T.espece=A.espece
WHERE T.elv_prod='12232099' AND T.espece='2' AND T.prod='1'
AND T.d_traite='10-MAR-2020' AND T.moment='S'
ORDER BY A.ipg;

Pour avoir les femelles non traites à UNE date et UN moment donné (testé)

SELECT elv_ne,id_animal
FROM geedoc.mise_traite
WHERE elv_prod='12232099' AND espece='2' AND prod='1' AND campagne='2020' AND d_fin_traite IS NULL
MINUS
SELECT elv_ne,id_animal
FROM geedoc.traite_anim
WHERE elv_prod='12232099' AND espece='2' AND prod='1' AND d_traite='10-MAR-2020' AND moment='S';

Autre façon de faire (testé) :

SELECT elv_ne,id_animal,d_debut_traite
FROM geedoc.mise_traite
WHERE elv_prod='12232099' AND espece='2' AND prod='1' AND campagne='2020' AND d_fin_traite IS NULL
AND (elv_ne,id_animal) NOT IN (SELECT elv_ne,id_animal
FROM geedoc.traite_anim
WHERE elv_prod='12232099' AND espece='2' AND prod='1' AND d_traite='10-MAR-2020' AND moment='S' );

Femelles mises à la traite mais non traites dans une campagne de traite :

SELECT M.elv_ne,M.id_animal,A.ipg,M.d_debut_traite,M.d_fin_traite
FROM geedoc.mise_traite M
LEFT JOIN geedoc.animal A
ON M.id_animal=A.id_animal AND M.elv_ne=A.elv_ne AND M.espece=A.espece
WHERE M.elv_prod=‘12232099’ AND M.espece=‘2’ AND M.prod=‘1’ AND M.campagne=‘2014’
AND (M.elv_ne,M.id_animal) NOT IN
(SELECT TA.elv_ne,TA.id_animal
FROM geedoc.traite_anim TA
WHERE TA.elv_prod=‘12232099’ AND TA.espece=‘2’ AND TA.prod=‘1’ AND TA.campagne=‘2014');

Sélectionner toutes les traites liées au CLO (testé)

SELECT TA.elv_ne,TA.id_animal,A.ipg,A.lignee,MB.no_lact,TA.d_traite,TA.moment,TR.no_clo, 
TA.lot_traite,TA.place,TA.qte_lait,TA.no_ech,TA.tb,TA.tp,TA.ccs,TA.uree,TA.ano_labo
FROM geedoc.traite_anim TA
LEFT JOIN geedoc.animal A
ON TA.id_animal=A.id_animal AND TA.elv_ne=A.elv_ne AND TA.espece=A.espece
LEFT JOIN geedoc.misebas MB
ON TA.id_animal=MB.id_animal AND TA.elv_ne=MB.elv_ne AND TA.espece=MB.espece AND TA.campagne=MB.campagne
LEFT JOIN geedoc.chantier_tr TR
ON TA.elv_prod=TR.elv_prod AND TA.campagne=TR.campagne AND TA.d_traite=TR.d_traite AND TA.moment=TR.moment
WHERE TA.elv_prod='12232099' AND TA.prod='1' AND TA.espece='2'
AND TA.campagne='2020' AND TR.no_clo IS NOT NULL;