1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| SELECT DISTINCT
EU.CODE_RNE AS RNE_etablissement,
-- Remplacement des espaces vides par un tiret
REPLACE(Element_S.CODE_STRUCTURE,' ', '-') AS division_classe,
CASE E.SEXE_ID
WHEN '1' THEN 'ANGB2'
WHEN '2' THEN 'ANGB2'
ELSE E.SEXE_ID
END) AS Certif_Langue,
-- Remplacement des 1 (garçons) par M et des 2 (filles) par F
CASE E.SEXE_ID
WHEN '1' THEN 'M' -- 'M.'
WHEN '2' THEN 'F' -- 'Mme'
ELSE E.SEXE_ID
END AS civilite,
LTRIM(E.NOM_ELEVE) AS nom_famille,
E.NOM_USAGE_ELEVE AS nom_usage,
-- Concaténation des prénoms de l'élève dans 1 seule colonne avec séparateur espace vide ' '
CONCAT_WS(', ',E.PRENOM_ELEVE,E.PRENOM2_ELEVE,E.PRENOM3_ELEVE) AS prenoms,
E.ID_NATIONAL AS ine,
CASE E.PAYS_ID when '1' then '100' else E.PAYS_ID end AS pays_naissance,
-- Affichage de la date au format DD/MM/YYYY
TO_CHAR(E.DATE_NAISS_ELE, 'DD/MM/YYYY') AS date_naissance,
D.CODE_DEPARTEMENT AS depCOM_naissance,
CI.CODE_COMMUNE_INSEE AS commune_naissance,
Pays.CODE_PAYS AS nationalite,
CASE A.PAYS_ID when '1' then '100' else A.PAYS_ID end AS pays_residence,
A.LIGNE1_ADRESSE AS adresse_1,
A.LIGNE2_ADRESSE AS adresse_2,
A.LIGNE3_ADRESSE AS adresse_3,
A.LIGNE4_ADRESSE AS adresse_4,
A.CODE_POSTAL AS code_postal,
A.LL_POSTAL AS localite,
E.TEL_PERSONNEL AS telephone,
E.EMAIL_ELEVE AS adresse_mail,
-- Remplacement des BTS et des TERMINALES 1BCGSEANG - 2BTNSEANG - 3BCPSEANG - 4BCGINTER
FORMATION.FORMATION_SUIVIE AS formation_suivie,
E.TEL_PORTABLE AS telephone_mobile
FROM MATABLE.ELEVE AS E
INNER JOIN MATABLE.MEF AS M
ON (M.MEF_ID = E.MEF_ID)
-- INNER JOIN MATABLE.OPTION_ELEVE as oe ON (OE.ELEVE_ID = E.ELEVE_ID)
-- INNER JOIN MATABLE.MATIERE_ENSEIGNEE as ME ON (ME.MATIERE_ENSEIGNEE_ID = OE.MATIERE_ENSEIGNEE_ID)
INNER JOIN MATABLE.ETAB_UAJ AS EU
ON (E.ETAB_UAJ_ID = EU.ETAB_UAJ_ID)
INNER JOIN MATABLE.PAYS AS Pays
ON (Pays.PAYS_ID = E.PAYS_ID)
INNER JOIN MATABLE.COMMUNE_INSEE AS CI
ON (E.COMMUNE_INSEE_ID = CI.COMMUNE_INSEE_ID)
INNER JOIN MATABLE.DEPT AS D
ON (D.DEPT_INSEE_ID = CI.DEPT_INSEE_ID)
INNER JOIN MATABLE.ELEVE_STRUCTURE AS ES
ON (ES.ELEVE_ID = E.ELEVE_ID)
INNER JOIN MATABLE.ELEMENT_STRUCTURE AS Element_S
ON (Element_S.STRUCTURE_ID = ES.STRUCTURE_ID)
INNER JOIN MATABLE.ELEVE_PERSONNE_ADRESSE AS EPA
ON (EPA.ELEVE_ID = E.ELEVE_ID)
INNER JOIN MATABLE.PERSONNE AS P
ON (EPA.PERSONNE_ID = P.PERSONNE_ID)
INNER JOIN MATABLE.ADRESSE AS A
ON (P.ADRESSE_ID = A.ADRESSE_ID)
INNER JOIN (VAlUES
('31131211220', 'BTS31211'), ('31131209220', 'BTS31209'),
('31131210220', 'BTS31210'), ('3113121022A', 'BTS31210'),
('31120008220', 'BTS20008'), ('31133419220', 'BTS33419'),
('31133414220', 'BTS334145'),('31133415220', 'BTS334145'),
('31133420220', 'BTS33420'), ('20211010112', 'BCGSEANG'),
('20211011112', 'BCGSEANG'), ('20212005112', 'BCGSEANG'),
('20212005111', 'BCGSEANG'), ('20213019112', 'BCGSEANG'),
('21220006112', 'BTNSEANG'), ('21220006117', 'BTNSEANG'),
('2122000611M', 'BTNSEANG'), ('21231017117', 'BTNSEANG'),
('21231017112', 'BTNSEANG'), ('2123101711M', 'BTNSEANG'),
('21231018117', 'BTNSEANG'), ('21231018112', 'BTNSEANG'),
('2123101811M', 'BTNSEANG'), ('2123100A11A', 'BTNSEANG'),
('21231019112', 'BTNSEANG'), ('21231019117', 'BTNSEANG'),
('2123101911M', 'BTNSEANG'), ('21233402112', 'BTNSEANG'),
('24723004332', 'BTNSEANG'), ('2472300A11A', 'BTNSEANG'),
('24723005332', 'BCPSEANG'), ('24723103332', 'BCPSEANG'),
('24723103337', 'BCPSEANG'), ('24723405332', 'BCPSEANG'),
('24733403332', 'BCPSEANG'), ('20211010111', 'BCGINTER'),
('20213019111', 'BCGINTER')
) AS FORMATION (CODE_MEF, FORMATION_SUIVIE)
ON FORMATION.CODE_MEF = M.CODE_MEF
WHERE
(
EU.CODE_RNE IN ('0010207M','0010208P','0010209K','0110210B','0110211W'
,'0410212V','0410213N','0410214P','0410214E','0410215M'
,'0410216U','0610217J','0610218C','0610219D','0610220V')
OR
M.CODE_MEF IN ('31131211220','31131209220','31131210220','31120008220'
,'31133419220','31133414220','31133415220','31133420220')
)
ORDER BY
EU.CODE_RNE ASC ; |
Partager