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
| SELECT DISTINCT
MATABLE.ETAB_UAJ.CODE_RNE AS RNE_etablissement,
-- Remplacement des espaces vides par un tiret
REPLACE(MATABLE.ELEMENT_STRUCTURE.CODE_STRUCTURE,' ', '-') AS division_classe,
REPLACE(REPLACE(MATABLE.ELEVE.SEXE_ID, '2', 'ANGB2'), '1', 'ANGB2') AS Certif_Langue,
-- Remplacement des 1 (garçons) par M et des 2 (filles) par F
REPLACE(REPLACE(MATABLE.ELEVE.SEXE_ID, '1', 'M'), '2', 'F') AS civilite,
LTRIM(MATABLE.ELEVE.NOM_ELEVE) AS nom_famille,
MATABLE.ELEVE.NOM_USAGE_ELEVE AS nom_usage,
-- Concaténation des prénoms de l'élève dans 1 seule colonne avec séparateur espace vide ' '
COALESCE(MATABLE.ELEVE.PRENOM_ELEVE,'')||' '||
COALESCE(MATABLE.ELEVE.PRENOM2_ELEVE,'')||' '||
COALESCE(MATABLE.ELEVE.PRENOM3_ELEVE,'') as prenoms,
MATABLE.ELEVE.ID_NATIONAL AS ine,
REPLACE(MATABLE.ELEVE.PAYS_ID, '1', '100') AS pays_naissance,
-- Affichage de la date au format DD/MM/YYYY
TO_CHAR(MATABLE.ELEVE.DATE_NAISS_ELE, 'DD/MM/YYYY') AS date_naissance,
MATABLE.DEPT.CODE_DEPARTEMENT AS depCOM_naissance,
MATABLE.COMMUNE_INSEE.CODE_COMMUNE_INSEE AS commune_naissance,
MATABLE.PAYS.CODE_PAYS AS nationalite,
REPLACE(MATABLE.ADRESSE.PAYS_ID, '1', '100') AS pays_residence,
MATABLE.ADRESSE.LIGNE1_ADRESSE AS adresse_1,
MATABLE.ADRESSE.LIGNE2_ADRESSE AS adresse_2,
MATABLE.ADRESSE.LIGNE3_ADRESSE AS adresse_3,
MATABLE.ADRESSE.LIGNE4_ADRESSE AS adresse_4,
MATABLE.ADRESSE.CODE_POSTAL AS code_postal,
MATABLE.ADRESSE.LL_POSTAL AS localite,
MATABLE.ELEVE.TEL_PERSONNEL AS telephone,
MATABLE.ELEVE.EMAIL_ELEVE AS adresse_mail,
-- Remplacement des BTS et des TERMINALES 1BCGSEANG - 2BTNSEANG - 3BCPSEANG - 4BCGINTER
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MATABLE.MEF.CODE_MEF, '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_suivie,
MATABLE.ELEVE.TEL_PORTABLE AS telephone_mobile
FROM MATABLE.ELEVE
INNER JOIN MATABLE.MEF ON (MATABLE.MEF.MEF_ID = MATABLE.ELEVE.MEF_ID)
INNER JOIN MATABLE.OPTION_ELEVE ON (MATABLE.OPTION_ELEVE.ELEVE_ID = MATABLE.ELEVE.ELEVE_ID)
INNER JOIN MATABLE.MATIERE_ENSEIGNEE ON (MATABLE.MATIERE_ENSEIGNEE.MATIERE_ENSEIGNEE_ID = MATABLE.OPTION_ELEVE.MATIERE_ENSEIGNEE_ID)
INNER JOIN MATABLE.ETAB_UAJ ON (MATABLE.ELEVE.ETAB_UAJ_ID = MATABLE.ETAB_UAJ.ETAB_UAJ_ID)
INNER JOIN MATABLE.COMMUNE_INSEE ON (MATABLE.ELEVE.COMMUNE_INSEE_ID = MATABLE.COMMUNE_INSEE.COMMUNE_INSEE_ID)
INNER JOIN MATABLE.DEPT ON (MATABLE.DEPT.DEPT_INSEE_ID = MATABLE.COMMUNE_INSEE.DEPT_INSEE_ID)
INNER JOIN MATABLE.PAYS ON (MATABLE.PAYS.PAYS_ID = MATABLE.ELEVE.PAYS_ID)
INNER JOIN MATABLE.ELEVE_STRUCTURE ON (MATABLE.ELEVE_STRUCTURE.ELEVE_ID = MATABLE.ELEVE.ELEVE_ID)
INNER JOIN MATABLE.ELEMENT_STRUCTURE ON (MATABLE.ELEMENT_STRUCTURE.STRUCTURE_ID = MATABLE.ELEVE_STRUCTURE.STRUCTURE_ID)
INNER JOIN MATABLE.ELEVE_PERSONNE_ADRESSE ON (MATABLE.ELEVE_PERSONNE_ADRESSE.ELEVE_ID = MATABLE.ELEVE.ELEVE_ID)
INNER JOIN MATABLE.PERSONNE ON (MATABLE.ELEVE_PERSONNE_ADRESSE.PERSONNE_ID = MATABLE.PERSONNE.PERSONNE_ID)
INNER JOIN MATABLE.ADRESSE ON (MATABLE.PERSONNE.ADRESSE_ID = MATABLE.ADRESSE.ADRESSE_ID)
WHERE ( MATABLE.ETAB_UAJ.CODE_RNE = '0010207M'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0010208P'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0010209K'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0110210B'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0110211W'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410212V'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410213N'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410214P'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410214E'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410215M'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0410216U'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0610217J'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0610218C'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0610219D'
OR MATABLE.ETAB_UAJ.CODE_RNE = '0610220V')
OR ( MATABLE.MEF.CODE_MEF = '31131211220'
OR MATABLE.MEF.CODE_MEF = '31131209220'
OR MATABLE.MEF.CODE_MEF = '31131210220'
OR MATABLE.MEF.CODE_MEF = '31120008220'
OR MATABLE.MEF.CODE_MEF = '31133419220'
OR MATABLE.MEF.CODE_MEF = '31133414220'
OR MATABLE.MEF.CODE_MEF = '31133415220'
OR MATABLE.MEF.CODE_MEF = '31133420220'
))
ORDER BY
MATABLE.ETAB_UAJ.CODE_RNE ASC ; |
Partager