select
tq_niveau+1 generation,
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE) origine,
iif(
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)=iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE),
'x',
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE)
) migration,
i2.nom nom_parent,
i2.prenom prenom_parent,
i.prenom prenom_enfant
from
(select tq_niveau, tq_cle_fiche from PROC_TQ_ASCENDANCE(5457,13,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and i.cle_pere = i2.cle_fiche
union
select
tq_niveau+1 generation,
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE) origine,
iif(
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)=iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE),
'x',
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE)
) migration,
i2.nom nom_parent,
i2.prenom prenom_parent,
i.prenom prenom_enfant
from
(select tq_niveau, tq_cle_fiche from PROC_TQ_ASCENDANCE(5457,13,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and i.cle_mere = i2.cle_fiche
Génération | Origine | Migration |
1 | Ménil-Ciboult (Le) | Périgny |
1 | Périgny | x |
2 | Culey-le-Patry | Périgny |
2 | Ménil-Ciboult (Le) | x |
2 | Périgny | x |
2 | Saint-Quentin-les-Chardonnerets | Ménil-Ciboult (Le) |
select
tq_niveau generation,
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE) origine,
iif(
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE) = iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE),
'x',
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)
) migration,
i.nom nomparent, i.prenom prenomparent, i2.nom nomenft, i2.prenom prenomenft
from
(select tq_niveau, tq_cle_fiche, tq_descendant from PROC_TQ_ASCENDANCE(5457,0,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and tq_descendant = i2.cle_fiche
select generation
,coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc)) as origine
,case
when coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc))
=coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc))
then 'x'
else
coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc))
end as migration
,nom_parent
,prenom_parent
,nom_enfant
,prenom_enfant
from
(select t.tq_niveau as generation
,n.ev_ind_ville as ville_nais_anc
,(select first 1 ev_fam_ville
from evenements_fam
where EV_FAM_KLE_FAMILLE=u.UNION_CLEF
and EV_FAM_TYPE = 'MARR'
and ev_fam_ville is not null
order by ev_fam_date_year,ev_fam_date_mois,ev_fam_date) as ville_mar_anc
,d.ev_ind_ville as ville_dc_anc
,n2.ev_ind_ville as ville_nais_desc
,(select first 1 ev_fam_ville
from evenements_fam
where EV_FAM_KLE_FAMILLE=u2.UNION_CLEF
and EV_FAM_TYPE = 'MARR'
and ev_fam_ville is not null
order by ev_fam_date_year,ev_fam_date_mois,ev_fam_date) as ville_mar_desc
,d2.ev_ind_ville as ville_dc_desc
,i.nom as nom_parent
,i.prenom as prenom_parent
,i2.nom as nom_enfant
,i2.prenom as prenom_enfant
from (select tq_niveau
,tq_cle_fiche
,tq_dossier
,tq_descendant
from PROC_TQ_ASCENDANCE(:indi,0,0,1)) t
inner join individu i on i.cle_fiche=t.tq_cle_fiche
left join EVENEMENTS_IND n on i.cle_fiche = n.EV_IND_KLE_FICHE and n.EV_IND_TYPE = 'BIRT'
left join t_union u on i.cle_pere = u.union_mari and i.cle_mere = u.union_femme
left join EVENEMENTS_IND d on i.cle_fiche = d.EV_IND_KLE_FICHE and d.EV_IND_TYPE = 'DEAT'
inner join individu i2 on i2.cle_fiche=t.tq_descendant
left join EVENEMENTS_IND n2 on i2.cle_fiche = n2.EV_IND_KLE_FICHE and n2.EV_IND_TYPE = 'BIRT'
left join t_union u2 on i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme
left join EVENEMENTS_IND d2 on i2.cle_fiche = d2.EV_IND_KLE_FICHE and d2.EV_IND_TYPE = 'DEAT')
where coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc)) is not null
and coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc)) is not null
order by 1,2
Vous verrez que l'utilisation des jointures avec les tables "individu" la rend bien plus rapide que lorsque ces jointures sont définies par des filtres dans la clause WHERE (100ms au lieu de 1,5s chez moi). Firebird s'y retrouve sans doûte plus facilement pour définir un plan utilisant des champs indexés permettant de retrouver les enregistrements.