Sujet: boa, requêtes et sql

[Tophe3860]

sujet : requête SQL et création de listes diverses et variées

auteurs : Horemans, Charlet, DDdeberdeux, et plein d'autres...

trucs & astuces proposés :

• mode d'emploi du BOA et générateur de requete
  
  • mode d'emploi "express"
  • mode d'emploi "pas à pas"
• établir des listes de recherches classiques
  
  • actes naissances manquants
  • actes décès manquants
  • actes de mariages manquants
• établir des listes de recherches sur mesure
  
  • requête "tout en un"
  • liste d'ascendance avec profession
  • rechercher les individus ayant exercé une profession
  • liste de toutes les personnes de la base ayant une photo d'identité
  • liste de descendance
  • liste des oncles et tantes
  • liste des maires
• intervenir sur la base
  
  • ajout massif devant le nom ou le prénom dans un nouveau dossier qui a reçu un import gedcom
  • mettre la première lettre des professions en minuscule
  • passer les "actes absents" en "actes à chercher
  
  Attention :
  Les éléments suivants modifient la base de données :
  - il faut se lancer en toute connaissance de cause,
  - il faut faire toutes les sauvegardes nécessaires avant de l'entreprendre.
  
  
  Si certains éléments non cités devaient l'être...
  faites-le savoir par un message privé, je les ajouterai, ainsi que leurs liens... 
  
  

[Tophe3860]

Mode d'emploi "express"
Faites un copier / coller des requêtes (ou adaptez-les...) dans :

• BOA, la Boite à Outils pour Ancestrologie
  
  • menu "plugins" / ligne "BOA" / menu "SQL" / champ "requêtes SQL"
  • bouton "exécuter requête", si la requête renvoie normalement un résultat
  • ou bouton "excécuter procédure", si aucun résultat n'est attendu

• Generation de requête SQL...
  
  • menu "outils" / ligne "Generation de requête SQL..." / onglet "SQL"
  • icone-bouton "lecture ".

    Remplacer le KLE_DOSSIER par le bon numéro de dossier, précision à trouver par le menu déroulant généalogies / dossiers de la base... du logiciel Ancestrologie

Mode d'emploi "pas à pas"

• D'accord, mais coller où?

• D'abord tu t'assures d'avoir la dernière version du BOA (V 1.7);
  
    Lorsque tu trouves des requêtes dans le forum, bien souvent la raison de cette requète et son utilisation sont décrites.
  
    Le BOA étant ouvert, tu vas dans l'onglet SQL. Tu vois apparaître plusieurs fenêtres dont une fenêtre centrale appelée "Requête SQL".
  Par défaut apparaît dans cette fenêtre l'indication: "select * from individu where kle_dossier=1".
  
    Lorsque tu veux utiliser une requête, le plus simple est de procéder par un copier-coller. Tu colles la requête dans la fenêtre centrale "Requête SQL", par dessus le select * ..... sinon tu l'effaces avant.
  Le cas échéant, si la requête s'adresse à un dossier particulier, dans le contenu de la requète tu recherches KLE_DOSSIER= n et tu remplaces n par le numéro de dossier en question.
  
    Si tu veux utiliser les Procédures présentes dans le BOA, il faut la faire précéder de EXECUTE PROCEDURE suivi de la procédure. Certaines procédures demandent un paramétrage, par exemple: PROC_MAJ_FORME_DATE(FORME,MODE_MAJ). Il faut alors donner à FORME la bonne valeur, même chose pour MODE_MAJ. Le plus souvent une recherche sur le forum te donne la solution. Exemple:
  EXECUTE PROCEDURE PROC_MAJ_FORME_DATE('LIT',2)
  
    Si la requête ne retourne aucun résultat (mise à jour, formatage de date, ....) tu fais la commande Exécuter procédure".
  Si la requète retourne un résultat, tu fais alors la commande Exécuter requête. Tu peux exporter le résultat par l'action clic droit de la souris sur celui-ci.
  
    Durant l'exécution, tu verras apparaître la mention Procédure en cours puis procédure terminée (ces textes sont approximatifs, mais c'est l'esprit). Le cas échéant, il faut valider les résultats.
  Bonne requête et surtout une sauvegarde de la base avant l'opération.

[Tophe3860]

lien vers le forum : un florilège de requêtes "classiques"

Liste des actes naissances manquants pour les individus porteurs d’un sosa, triée par ville et par date

Code: [Sélectionner]

SELECT I.NUM_SOSA as SOSA, I.NOM, I.PRENOM,
E.EV_IND_DATE_WRITEN as Dates, E.EV_IND_VILLE as Lieu
FROM INDIVIDU I, EVENEMENTS_IND E
WHERE I.KLE_DOSSIER = 2
AND I.NUM_SOSA IS NOT NULL
AND E.EV_IND_KLE_FICHE = I.CLE_FICHE
AND E.EV_IND_KLE_DOSSIER = I.KLE_DOSSIER
AND E.EV_IND_TYPE = 'BIRT'
AND (E.EV_IND_ACTE = 0 or E.EV_IND_ACTE IS NULL)
ORDER BY E.EV_IND_VILLE, E.EV_IND_DATE, I.NUM_SOSA
Liste des actes décès manquants pour les individus porteurs d’un sosa, triée par ville et par date

Code: [Sélectionner]

SELECT I.NUM_SOSA as SOSA, I.NOM, I.PRENOM,
E.EV_IND_DATE_WRITEN as Dates, E.EV_IND_VILLE as Lieu
FROM INDIVIDU I, EVENEMENTS_IND E
WHERE I.KLE_DOSSIER = 2
AND I.NUM_SOSA IS NOT NULL
AND E.EV_IND_KLE_FICHE = I.CLE_FICHE
AND E.EV_IND_KLE_DOSSIER = I.KLE_DOSSIER
AND E.EV_IND_TYPE = 'DEAT'
AND (E.EV_IND_ACTE = 0 or E.EV_IND_ACTE IS NULL)
ORDER BY E.EV_IND_VILLE, E.EV_IND_DATE, I.NUM_SOSA
Liste des actes de mariages manquants pour les porteurs d’un sosa, triée par ville et par date

Code: [Sélectionner]

SELECT I.NUM_SOSA , I.NOM, I.PRENOM, I1.NUM_SOSA AS SOSA_CJT, I1.NOM AS NOM_CJT, I1.PRENOM AS PRENOM_CJT,
E.EV_FAM_DATE_WRITEN AS DATES, E.EV_FAM_VILLE AS LIEU
FROM T_UNION T, INDIVIDU I, INDIVIDU I1, EVENEMENTS_FAM E
WHERE T.KLE_DOSSIER = 2
AND I.CLE_FICHE = T.UNION_MARI
AND I.KLE_DOSSIER = T.KLE_DOSSIER
AND I.NUM_SOSA IS NOT NULL
AND I1.CLE_FICHE = T.UNION_FEMME
AND I1.KLE_DOSSIER = T.KLE_DOSSIER
AND I1.NUM_SOSA IS NOT NULL
AND E.EV_FAM_KLE_FAMILLE = T.UNION_CLEF
AND E.EV_FAM_TYPE = 'MARR'
AND (E.EV_FAM_ACTE = 0 OR E.EV_FAM_ACTE IS NULL)
ORDER BY E.EV_FAM_VILLE, E.EV_FAM_DATE, I.NOM, I.PRENOM, I.NUM_SOSA, I1.NOM, I1.PRENOM,
I1.NUM_SOSA
Liste des actes de naissance manquants triée par ville et par date

Code: [Sélectionner]

SELECT I.CLE_FICHE AS NIP, I.NOM, I.PRENOM, R.REF_EVE_LIB_LONG AS EVENEMENT, E.EV_IND_DATE_WRITEN AS DATE_EVENEMENT, E.EV_IND_VILLE AS VILLE
FROM INDIVIDU I, EVENEMENTS_IND E, REF_EVENEMENTS R
WHERE I.KLE_DOSSIER = 2
AND E.EV_IND_KLE_FICHE = I.CLE_FICHE
AND E.EV_IND_TYPE = 'BIRT'
AND (E.EV_IND_ACTE = 0 OR E.EV_IND_ACTE IS NULL)
AND R.REF_EVE_LIB_COURT = E.EV_IND_TYPE
ORDER BY E.EV_IND_VILLE, E.EV_IND_DATE, I.NOM, I.PRENOM, I.CLE_FICHE
Liste des actes de décès manquants triée par ville et par date

Code: [Sélectionner]

SELECT I.CLE_FICHE AS NIP, I.NOM, I.PRENOM, R.REF_EVE_LIB_LONG AS EVENEMENT, E.EV_IND_DATE_WRITEN AS DATE_EVENEMENT, E.EV_IND_VILLE AS VILLE
FROM INDIVIDU I, EVENEMENTS_IND E, REF_EVENEMENTS R
WHERE I.KLE_DOSSIER = 2
AND E.EV_IND_KLE_FICHE = I.CLE_FICHE
AND E.EV_IND_TYPE = 'DEAT'
AND (E.EV_IND_ACTE = 0 OR E.EV_IND_ACTE IS NULL)
AND R.REF_EVE_LIB_COURT = E.EV_IND_TYPE
ORDER BY E.EV_IND_VILLE, E.EV_IND_DATE, I.NOM, I.PRENOM, I.CLE_FICHE
Liste des actes de mariage manquants triée par ville et par date

Code: [Sélectionner]

SELECT I.NOM, I.PRENOM, I1.NOM AS NOM_CJT, I1.PRENOM AS PRENOM_CJT,
E.EV_FAM_DATE_WRITEN AS DATES, E.EV_FAM_VILLE AS LIEU
FROM T_UNION T, INDIVIDU I, INDIVIDU I1, EVENEMENTS_FAM E
WHERE T.KLE_DOSSIER = 2
AND I.CLE_FICHE = T.UNION_MARI
AND I.KLE_DOSSIER = T.KLE_DOSSIER
AND I1.CLE_FICHE = T.UNION_FEMME
AND I1.KLE_DOSSIER = T.KLE_DOSSIER
AND E.EV_FAM_KLE_FAMILLE = T.UNION_CLEF
AND E.EV_FAM_TYPE = 'MARR'
AND (E.EV_FAM_ACTE = 0 OR E.EV_FAM_ACTE IS NULL)
ORDER BY E.EV_FAM_VILLE, E.EV_FAM_DATE, I.NOM, I.PRENOM, I1.NOM, I1.PRENOM

[Tophe3860]

une requête "tout en un"

Un export vers Excel permet plein de chose: d'éclater la liste par type d'événement, de trier, d'utiliser le filtre automatique, etc...
Avant utilisation il faut indiquer le bon numéro de dossier ainsi que le département.
Attention, les actes existants sont notés 1, les absents par un vide. Selon la version utilisée au moment de l'enregistrement des données, des événements pour lesquels les actes ne sont pas disponibles sont marqués 0. Il suffit de les éliminer pour uniformiser le tout.

Code: [Sélectionner]

SELECT DISTINCT
I.NUM_SOSA AS SOSA
, I.NOM
, I.PRENOM
, N.EV_IND_DATE_WRITEN AS DATE_NAISSANCE
, EXTRACT (DAY FROM N.EV_IND_DATE) AS JJ
, EXTRACT (MONTH FROM N.EV_IND_DATE) AS MM
, EXTRACT (YEAR FROM N.EV_IND_DATE) AS AA
, N.EV_IND_DATE_YEAR AS AN_NAISSANCE
, N.EV_IND_VILLE AS VILLE_NAISSANCE
, N.EV_IND_DEPT AS DEPT_NAISSANCE
, N.EV_IND_ACTE AS ACTE_NAISSANCE
, D.EV_IND_DATE_WRITEN AS DATE_DECES
, EXTRACT (DAY FROM D.EV_IND_DATE) AS JJ
, EXTRACT (MONTH FROM D.EV_IND_DATE) AS MM
, EXTRACT (YEAR FROM D.EV_IND_DATE) AS AA
, D.EV_IND_DATE_YEAR AS AN_DECES
, D.EV_IND_VILLE AS VILLE_DECES
, D.EV_IND_DEPT AS DEPT_DECES
, D.EV_IND_ACTE AS ACTE_DECES
, R.REF_EVE_LIB_LONG AS TYPE_UNION
, F.EV_FAM_DATE_WRITEN AS DATE_UNION
, EXTRACT (DAY FROM F.EV_FAM_DATE) AS JJ
, EXTRACT (MONTH FROM F.EV_FAM_DATE) AS MM
, EXTRACT (YEAR FROM F.EV_FAM_DATE) AS AA
, F.EV_FAM_DATE_YEAR AS AN_UNION
, F.EV_FAM_VILLE AS VILLE_UNION
, F.EV_FAM_DEPT AS DEPT_UNION
, F.EV_FAM_ACTE AS ACTE_UNION
, IC.NOM AS NOM_CONJOINT
, IC.PRENOM AS PRENOM_CONJOINT
, IC.NUM_SOSA AS SOSA_CONJOINT
FROM INDIVIDU I
LEFT OUTER JOIN EVENEMENTS_IND N ON I.CLE_FICHE = N.EV_IND_KLE_FICHE AND N.EV_IND_TYPE= 'BIRT'
LEFT OUTER JOIN EVENEMENTS_IND D ON I.CLE_FICHE = D.EV_IND_KLE_FICHE AND D.EV_IND_TYPE= 'DEAT'
LEFT OUTER JOIN T_UNION U ON I.CLE_FICHE = U.UNION_MARI OR I.CLE_FICHE = U.UNION_FEMME
LEFT OUTER JOIN INDIVIDU IC ON (U.UNION_MARI = IC.CLE_FICHE OR U.UNION_FEMME = IC.CLE_FICHE) AND IC.CLE_FICHE <> I.CLE_FICHE
LEFT OUTER JOIN EVENEMENTS_FAM F ON U.UNION_CLEF = F.EV_FAM_KLE_FAMILLE
LEFT OUTER JOIN REF_EVENEMENTS R ON F.EV_FAM_TYPE =R .REF_EVE_LIB_COURT
WHERE I.KLE_DOSSIER =2
AND ( N.EV_IND_DEPT='Nord' OR D.EV_IND_DEPT='Nord' OR F.EV_FAM_DEPT='Nord' )
ORDER BY I.NOM, I.PRENOM
liste d'ascendance avec profession

Code: [Sélectionner]

select p.*
      ,(select OCCUPATION from PROC_DERNIER_METIER(p.cle_fiche))
from PROC_ETAT_ASCENDANCE(NIP, 0, 0, MODE) p

en remplaçant NIP par la valeur du NIP de l'individu de départ, et MODE par:
0 pour les ascendants sans implexes
1 y ajouter les implexes, la première fois qu'ils sont trouvés
2 tous les ascendants (implexes sans limitation de niveau)
On pourrait afficher tous les métiers, mais à ce moment, les individus ayant plusieurs métiers seraient répétés autant de fois que de métier dans leur vie.
Vous verrez les noms des champs qui sont sortis. Si vous ne voulez en voir que certains, il faudra, à la place de p.*, les énumérer dans l'ordre voulu, précédés de p. avec la virgule comme séparateur.

rechercher les individus ayant exercé une profession

select i.cle_fiche
      ,i.nom
      ,i.prenom
      ,e.ev_ind_description
from individu i
inner join evenements_ind e
        on e.ev_ind_kle_fiche=i.cle_fiche
       and e.ev_ind_type='OCCU'
where i.kle_dossier=1
  and upper(e.ev_ind_description) containing upper('ouvrier')

en remplaçant le texte en rouge par ce qui correspond à ta recherche.
Si la recherche doit être plus stricte tu peux remplacer "containing" par "starting with" ou par "=" et supprimer les "upper".

liste  de toutes les personnes de la base ayant une photo d'identité

SELECT DISTINCT INDIVIDU.CLE_FICHE as NIP,
INDIVIDU.NOM,
INDIVIDU.PRENOM,
INDIVIDU.DATE_NAISSANCE,
INDIVIDU.DATE_DECES
FROM INDIVIDU
INNER JOIN MEDIA_POINTEURS ON (INDIVIDU.CLE_FICHE = MEDIA_POINTEURS.MP_CLE_INDIVIDU)
WHERE MEDIA_POINTEURS.MP_IDENTITE = 1
AND INDIVIDU.KLE_DOSSIER = 1
ORDER BY INDIVIDU.NOM

Changer le n° de dossier si nécessaire

liste de descendance

Dans le BOA (ou un autre requêteur, ou depuis un tableur) copiez la requête suivante:

Code: [Sélectionner]

select i.niveau
      ,i.sosa
      ,i.nom
      ,i.prenom
      ,i.date_naissance as naissance
      ,i.date_deces as deces
      ,i.age_au_deces as age
      ,i.occupation
      ,i.ordre
      ,c.nom as nom_conjoint
      ,c.prenom as prenom_conjoint
      ,c.date_naissance as naissance_conjoint
      ,c.date_deces as deces_conjoint
      ,c.date_mariage
from proc_descendance(:clef,0,:dossier) i
left join proc_trouve_conjoints(0,i.cle_fiche) c on  1=1
order by i.niveau,i.sosa,c.annee_mariageRemplacez :clef et :dossier par le NIP de l'individu de départ et le n° de votre dossier, et exécutez la requête.
L'inconvénient, c'est que pour les individus mariés plusieurs fois il y a autant de lignes que de conjoints, et que le conjoint indiqué n'est pas obligatoirement le parent des descendants.

liste des oncles et tantes

En attendant qu'un jour on complète le document liste des oncles/tantes avec leurs conjoints (peut-être une option?), exécutez la requête suivante dans le BOA ou un autre requêteur:

Code: [Sélectionner]

select t.cle_fiche as NIP
      ,t.nom||coalesce(' '||t.prenom,'') as NOM
      ,case t.sexe
         when 1 then 'M'
         when 2 then 'F'
         else 'I'
       end as SEXE
      ,t.date_naissance as NAISSANCE
      ,t.date_deces as DECES
      ,c.cle_fiche as NIP_CONJOINT
      ,c.nom||coalesce(' '||c.prenom,'') as NOM_CONJOINT
      ,c.date_naissance as NAISSANCE_CONJOINT
      ,c.date_deces as DECES_CONJOINT
from proc_trouve_oncles_tantes(:NIP,0,0) t
     left join proc_trouve_conjoints(0,t.cle_fiche) c on 1=1
order by t.sosa,c.ordre_unionen remplaçant :NIP par le N° de votre individu.

liste des maires

Code: [Sélectionner]

select i.nom
      ,i.prenom
      ,e.ev_ind_description
      ,e.ev_ind_date_writen
      ,e.ev_ind_cp
      ,e.ev_ind_ville
from evenements_ind e
     inner join individu i on i.cle_fiche=e.ev_ind_kle_fiche
                           and i.kle_dossier=1
where e.ev_ind_type='TITL'
  and upper(e.ev_ind_description) containing 'MAIRE'
order by i.nom,i.prenomdevrait convenir, en remplaçant le "1" du dossier par le n° du votre.
J'utilise le upper et containing pour "élargir" un peu la recherche en autorisant la sélection même si vous avez écrit "maire" au lieu de "Maire" et pas toujours seul dans la description.

[Tophe3860]

ajout massif devant le nom ou le prénom dans un nouveau dossier qui a reçu un import gedcom

Code: [Sélectionner]

update individu
set nom = '1' || nom
where  KLE_DOSSIER = 2

2, si tu as importé ton gedcom dans le dossier 2.
Dans le BOA, l'éxécution est obtenue en cliquant sur "Exécuter une procédure" puisque cette requette ne retourne aucun résultat. Ne pas oublier de valider les modifications en quittant le BOA.

Ensuite il suffit d'importer ce dossier 2dans ton dossier de travail. Tous les nouveaux individus seront listés en tête du répertoire "...".
J'ai fait un essai en ajoutant un "-" au lieu du "1" en tête du nom, mais l'ordre n'est plus le même. Curieusement dans le répertoire "..." les noms commençant par "-" sont mélangés avec les autres, comme si le signe "-" n'existait pas, alors qu'ils n'existent pas dans le répertoire par lettre.
Cette solution a l'inconvénient de ne pas faciliter le rapprochement des individus par leur nom. Aussi je te conseillerai plutôt d'ajouter le symbole de reconnaissance au début du prénom. Le tri du répertoire sur la colonne prénom permet de les regrouper, alors que le tri par nom ne les sépare pas des individus de même patronyme.

mettre la première lettre des professions en minuscule

Pour remettre en minuscule la première lettre de champ description concernant les profession, sans toucher au reste de la description (qui pourrait contenir une majuscule intentionnelle comme dans "employé des PTT"), exécutez la requête suivante:

Code: [Sélectionner]

update evenements_ind  e
set ev_ind_description=lower(substring(ev_ind_description from 1 for 1))
                       ||substring(ev_ind_description from 2)
where ev_ind_kle_dossier=:VotreDossier
  and ev_ind_type='OCCU'
  and char_length(ev_ind_description)>1(Cliquer sur "Exécuter une procédure" dans le BOA.)
Dans la liste des professions ne devraient rester en majuscule que les cas où la profession est la deuxième ou troisième entrée dans le même événement individuel (professions séparées par des virgules).

passer les "actes absents" en "actes à chercher"

Depuis que j'ai ajouté les types d'actes "trouvé", "à chercher" etc... j'ai un grand nombre d'actes 0=absents qui doivent passer dans le type -2=à chercher.
J'ai voulu concentrer mes recherches sur les actes devenus disponibles en ligne aux archives départementales de la Mayenne et du Morbihan, et uniquement sur mes ascendants directs.
Mais modifier un à un tous les événements est un travail à réserver aux bénédictins. J'ai donc fait les 2 requêtes suivantes :
- l'une pour les événements individuels
- l'autre pour les événements familiaux:

Code: [Sélectionner]

update evenements_ind ei
set ei.ev_ind_acte=-2
where ei.ev_ind_kle_dossier=1
and (ei.ev_ind_acte=0 or ei.ev_ind_acte is null)
and ei.ev_ind_type in ('BIRT','DEAT','CHR')
and ei.ev_ind_dept in ('Morbihan','Mayenne')
and (select num_sosa from individu where cle_fiche=ei.ev_ind_kle_fiche)>0

Code: [Sélectionner]

update evenements_fam ei
set ei.ev_fam_acte=-2
where ei.ev_fam_kle_dossier=1
and (ei.ev_fam_acte=0 or ei.ev_fam_acte is null)
and ei.ev_fam_type='MARR'
and ei.ev_fam_dept in ('Morbihan','Mayenne')
and exists (select * from t_union u
inner join individu i on i.cle_fiche in (u.union_mari,u.union_femme) and
i.num_sosa>0
where u.union_clef=ei.ev_fam_kle_famille)

Si la recherche est limitée à quelques villes on peut limiter la sélection à ces villes en ajoutant dans la clause

Code: [Sélectionner]

where
and ei.ev_ind_ville in ('Vannes','Laval')
par exemple.

Il faut évidemment les exécuter (exécuter procédure dans le BOA) après une numérotation SOSA si on veut limiter la sélection aux ascendants.
Maintenant je peux exploiter la fiche des actes à chercher.