Bonjour,
Je travaille sous Oracle 9.2.0.1.0
Comme vous pouvez le constater la requête suivante contient des parties qui se répétent, je me demande donc comment je peux réduire sa taille.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Je vous remercie d'avance pour votre aide