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 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
| select a.NIP, a.NOM, a.PRENOM,a.DATE_ENT,a.DATE_SOR,b.DATE_EXAMEN,c.HEURE_EXAMEN,a.NIQ,a.LIBABR,a.REPONSE,a.TYPEQ
from (select distinct p.NIP, p.NOM, p.PRENOM,sj.DATE_ENT,sj.DATE_SOR,q3.NIQ,q3.LIBABR,r3.REPONSE,q3.TYPEQ
from PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.BM_REP_S r3,
PENSOINS.BM_TH_S th,
PENSOINS.SEJOUR sj,
PENSOINS.PATIENT p
where lib2.PARENTE like '674,%'
and lib2.NIQ = q3.NIQ
and lib2.NILIB = r3.NILIB
and th.NITH = lib2.NITH
and th.NISEJOUR = sj.NISEJOUR
and p.NIPATIENT = sj.NIPATIENT
and r3.REPONSE is not NULL
and p.NOM not like 'ZZ%'
and sj.DATE_SOR is not NULL
and (q3.LIBABR like 'Numéro de dossier appareil EFR%'
or q3.LIBABR like 'Date de l''examen%'
or q3.LIBABR like 'Heure de l''examen%'
or q3.LIBABR like 'Date de naissance%'
or q3.LIBABR like 'Age (ans)%'
or q3.LIBABR like 'Taille si Femme (m)%'
or q3.LIBABR like 'Taille si Homme (m)%'
or q3.LIBABR like 'Poids (kg)%'
or q3.LIBABR like 'BMI si Femme (F)%'
or q3.LIBABR like 'BMI si Homme (M)%'
or q3.LIBABR like 'Interprétation du résultat BMI%'
or q3.LIBABR like 'Tabac%'
or q3.LIBABR like 'CO expiré (ppm)%'
or q3.LIBABR like 'Si protocole : Nom du protocole%'
or q3.LIBABR like 'Information/Consentement%'
or q3.LIBABR like 'Prescription médicale pertinente%'
or q3.LIBABR like 'Pathologie respiratoire connue%'
or q3.LIBABR like 'Autre pathologie respiratoire%'
or q3.LIBABR like 'Bilan d''anomalies radiologiques%'
or q3.LIBABR like 'Autre anomalie radiologique%'
or q3.LIBABR like 'Bilan de signes fonct. respirat%'
or q3.LIBABR like 'Pathologie extra-pulm. connue%'
or q3.LIBABR like 'Autre pathologie extra pulmonair%'
or q3.LIBABR like 'Bilan de SAS%'
or q3.LIBABR like 'Divers (bilans)%'
or q3.LIBABR like 'ATCD respiratoires%'
or q3.LIBABR like 'Autre ATCD respiratoire%'
or q3.LIBABR like 'Traitement à visée respiratoire%'
or q3.LIBABR like 'Nom des médicaments%'
or q3.LIBABR like 'Classe thérapeutique%'
or q3.LIBABR like 'Autres médicaments%'
or q3.LIBABR like 'Signes fonctionnels respirat%'
or q3.LIBABR like 'Signes cliniques respiratoires%'
or q3.LIBABR like 'Autres signes cliniques resp%'
or q3.LIBABR like 'Date d''apparition de la dyspnée%'
or q3.LIBABR like 'Conditions de survenue%'
or q3.LIBABR like 'Grades MRC dyspnée d''effort%'
or q3.LIBABR like 'Composant physique SF-36%'
or q3.LIBABR like 'Composant mental SF-36%'
or q3.LIBABR like 'Syndrome obstructif%'
or q3.LIBABR like 'Effet du bronchodilatateur%'
or q3.LIBABR like 'Qualité de l''exécution%'
or q3.LIBABR like 'BPCO post-tabagique%'
or q3.LIBABR like 'Emphysème pulmonaire probable%'
or q3.LIBABR like 'Asthme%'
or q3.LIBABR like 'Bronchiolite oblitérante%'
or q3.LIBABR like 'Pathologie restrictive%'
or q3.LIBABR like 'Pathologie musculaire%'
or q3.LIBABR like 'Atteinte vasculaire pulmonaire%'
or q3.LIBABR like 'Conclusion de l''examen EFR%'
or q3.LIBABR like 'Quel type de fumeur êtes vous%'
or q3.LIBABR like '%Cigarettes fumées (Paquet/Année)%'
or q3.LIBABR like '%Consom.tabac roulé(gr/semaine)%'
or q3.LIBABR like '%Nbre de cigarettes fumées/jour%'
or q3.LIBABR like '%Année du sevrage(cigarettes)%'
or q3.LIBABR like '%Tabac à pipe consommé gr/semaine%'
or q3.LIBABR like '%Nbre d''année consom tabac/pipe%'
or q3.LIBABR like '%Année du sevrage (cigares)%'
or q3.LIBABR like '%Tabac à pipe consommé gr/semaine%'
or q3.LIBABR like '%Nbre de cigares/jour%'
or q3.LIBABR like '%Nbre d''années de consom.cigares%'
or q3.LIBABR like '%Année du sevrage (tabac à pipe)%'
or q3.LIBABR like '%Quel type de greffe pulmonaire%'
or q3.LIBABR like '%Date de la greffe%'
or q3.LIBABR like 'Age lors découverte de l''asthme%'))a,
(select distinct p.NIP, p.NOM, p.PRENOM,sj.DATE_ENT,sj.DATE_SOR,r3.REPONSE AS DATE_EXAMEN
from PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.BM_REP_S r3,
PENSOINS.BM_TH_S th,
PENSOINS.SEJOUR sj,
PENSOINS.PATIENT p
where lib2.PARENTE like '674,%'
and lib2.NIQ = q3.NIQ
and lib2.NILIB = r3.NILIB
and th.NITH = lib2.NITH
and th.NISEJOUR = sj.NISEJOUR
and p.NIPATIENT = sj.NIPATIENT
and r3.REPONSE is not NULL
and p.NOM not like 'ZZ%'
and sj.DATE_SOR is not NULL
and q3.LIBABR like 'Date de l''examen')b,
(select distinct p.NIP, p.NOM, p.PRENOM,sj.DATE_ENT,sj.DATE_SOR,r3.REPONSE AS HEURE_EXAMEN
from PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.BM_REP_S r3,
PENSOINS.BM_TH_S th,
PENSOINS.SEJOUR sj,
PENSOINS.PATIENT p
where lib2.PARENTE like '674,%'
and lib2.NIQ = q3.NIQ
and lib2.NILIB = r3.NILIB
and th.NITH = lib2.NITH
and th.NISEJOUR = sj.NISEJOUR
and p.NIPATIENT = sj.NIPATIENT
and r3.REPONSE is not NULL
and p.NOM not like 'ZZ%'
and sj.DATE_SOR is not NULL
and q3.LIBABR like 'Heure de l''examen')c
where a.NIP = b.NIP
and a.DATE_ENT = b.DATE_ENT
and b.NIP = c.NIP
and c.DATE_ENT = b.DATE_ENT
union all
SELECT a.nip, a.nom, a.prenom, a.date_ent,a.date_sor,c.DATE_EXAMEN,d.HEURE_EXAMEN, b.code, b.libelle, NULL, 0
FROM (SELECT DISTINCT p.NIP, p.NOM, p.PRENOM, sj.DATE_ENT,sj.DATE_SOR
FROM PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.PATIENT p,
PENSOINS.SEJOUR sj,
PENSOINS.BM_TH_S th
WHERE lib2.nith = th.nith
AND lib2.parente LIKE '674,%'
AND p.nipatient = sj.nipatient
AND sj.nisejour = th.nisejour
AND th.niquest = q3.niquest) a,
(SELECT 8341.5 AS code, 'fumeur' AS libelle FROM DUAL
UNION ALL
SELECT 8342.5 AS code, 'fumeur de cigarettes' AS libelle FROM DUAL
UNION ALL
SELECT 8346.5 AS code, 'fumeur de tabac/pipe' AS libelle FROM DUAL
UNION ALL
SELECT 8349.5 AS code, 'fumeur de cigares' AS libelle FROM DUAL
UNION ALL
SELECT 8352.5 AS code, 'greffe pulmonaire' AS libelle FROM DUAL
UNION ALL
SELECT 8354.5 AS code, 'asthme' AS libelle FROM DUAL
)b,
(select distinct p.NIP, p.NOM, p.PRENOM,sj.DATE_ENT,sj.DATE_SOR,r3.REPONSE AS DATE_EXAMEN
from PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.BM_REP_S r3,
PENSOINS.BM_TH_S th,
PENSOINS.SEJOUR sj,
PENSOINS.PATIENT p
where lib2.PARENTE like '674,%'
and lib2.NIQ = q3.NIQ
and lib2.NILIB = r3.NILIB
and th.NITH = lib2.NITH
and th.NISEJOUR = sj.NISEJOUR
and p.NIPATIENT = sj.NIPATIENT
and r3.REPONSE is not NULL
and p.NOM not like 'ZZ%'
and sj.DATE_SOR is not NULL
and q3.LIBABR like 'Date de l''examen')c,
(select distinct p.NIP, p.NOM, p.PRENOM,sj.DATE_ENT,sj.DATE_SOR,r3.REPONSE AS HEURE_EXAMEN
from PENSOINS.BM_LIB_S lib2,
PENSOINS.C_QUESTION q3,
PENSOINS.BM_REP_S r3,
PENSOINS.BM_TH_S th,
PENSOINS.SEJOUR sj,
PENSOINS.PATIENT p
where lib2.PARENTE like '674,%'
and lib2.NIQ = q3.NIQ
and lib2.NILIB = r3.NILIB
and th.NITH = lib2.NITH
and th.NISEJOUR = sj.NISEJOUR
and p.NIPATIENT = sj.NIPATIENT
and r3.REPONSE is not NULL
and p.NOM not like 'ZZ%'
and sj.DATE_SOR is not NULL
and q3.LIBABR like 'Heure de l''examen')d
where a.NIP = c.NIP
and a.DATE_ENT = c.DATE_ENT
and d.NIP = c.NIP
and c.DATE_ENT = d.DATE_ENT
order by 2,4,6,8 |
Partager