bonjour,
je voudrai rajouter dans ce tableau une colonne "autres sosa" contenant tous les numéros sosa de chaque individu, est ce possible? ou faut il attendre une future version?
tableau:
SELECT DISTINCT
I.NUM_SOSA AS SOSA
, I.NOM
, I.PRENOM
, N.EV_IND_VILLE AS VILLE_NAISSANCE
, N.EV_IND_DATE_YEAR AS ANNEE_NAISSANCE
, N.EV_IND_DATE_WRITEN AS DATE_NAISSANCE
, D.EV_IND_VILLE AS VILLE_DECES
, D.EV_IND_DATE_YEAR AS ANNEE_DECES
, D.EV_IND_DATE_WRITEN AS DATE_DECES
, F.EV_FAM_VILLE AS VILLE_UNION
, F.EV_FAM_DATE_YEAR AS ANNEE_UNION
, F.EV_FAM_DATE_WRITEN AS DATE_UNION
, R.REF_EVE_LIB_LONG AS TYPE_UNION
, IC.NOM AS NOM_CONJOINT
, IC.PRENOM AS PRENOM_CONJOINT
, IC.NUM_SOSA AS SOSA_CONJOINT
, P.NOM AS NOM_PERE
, P.PRENOM AS PRENOM_PERE
, P.NUM_SOSA AS NUM_SOSA_PERE
, M.NOM AS NOM_MERE
, M.PRENOM AS PRENOM_MERE
, M.NUM_SOSA AS NUM_SOSA_MERE
, I.SEXE
, I.CLE_FICHE
, IC.CLE_FICHE AS CLE_FICHE_CONJOINT
, N.EV_IND_SUBD AS SUBDIVISION_NAISSANCE
, D.EV_IND_SUBD AS SUBDIVISION_DECES
, PROF. EV_IND_DESCRIPTION AS PROFESSION
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 EVENEMENTS_IND PROF ON I.CLE_FICHE = PROF.EV_IND_KLE_FICHE AND PROF.EV_IND_TYPE= 'OCCU'
LEFT OUTER JOIN INDIVIDU P ON I.CLE_PERE = P.CLE_FICHE
LEFT OUTER JOIN INDIVIDU M ON I.CLE_MERE = M.CLE_FICHE
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 (P.NUM_SOSA >0 OR M.NUM_SOSA >0 OR I.NUM_SOSA >0)
ORDER BY I.NUM_SOSA