Simplifier requête SQL

**CliffeCSTL** · 08/04/2015, 09h28

Bonjour,

Je suis débutant en SQL et je reprend le travail d'une autre personne qui à développé cette requête :

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
SELECT
    pat.nom NOM,
    pat.sexe SEXE,
    pat.nip ipp,
    substr(datenais, 1, 4) ANNEE_NAISSANCE,
    substr(datenais, 5, 2) MOIS_NAISSANCE,
    substr(datenais, 7, 2) JOUR_NAISSANCE,
    decode(pat.PATRONYME, null, pat.NOM, '', pat.NOM, pat.PATRONYME) NOM_NAISSANCE,
    pat.prenom PRENOM,
    infpat.NUMSECU NSS,
    decode(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 6, 2)) DEP_NAISSANCE,
    decode(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 8, 3)) VILLE_NAISSANCE,
    decode(srv.nietablis, 1, 'MZ', 3, 'MZ', 4, 'MZ', 5, 'MZ', 8, 'MZ', 9, 'MZ', 10, 'TH', 11, 'TH', 12, 'TH', 13, 'TH', 14, 'TH', 15, 'TH', 16, 'TH', 17, 'TH', 18, 'TH', 19, 'TH', 20, 'MZ', 21, 'TH', 22, 'MZ', 23, 'TH', 25, 'TH', 24, 'MZ', 'Autre') etb,
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4993 AND BM_TH_S.nith = th.nith) PatientNiveauEtudes049, 
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5023 AND BM_TH_S.nith = th.nith) PatientSituationGeo050,
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4988 AND BM_TH_S.nith = th.nith) PAtientEnvoyePar051,
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6025 AND BM_TH_S.nith = th.nith) DclEtatDemence052,
    (SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6031 AND BM_TH_S.nith = th.nith) DclAnneePremierDiag053,
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6035 AND BM_TH_S.nith = th.nith) DclDiagnostic054,
    (SELECT BM_REP_S.CODE    FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6026 AND BM_TH_S.nith = th.nith) DclTypeActe055,
    (SELECT substr(BM_REP_S.REPONSE,0,2)||substr(BM_REP_S.REPONSE,4,2)||substr(BM_REP_S.REPONSE,7,4) FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4964 and BM_TH_S.nith = th.nith) DclDateActe056, 
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6567 and BM_TH_S.nith = th.nith ),null,'',(SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6567 and BM_TH_S.nith = th.nith )) DclMMSE057,
    (SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5015 and BM_TH_S.nith = th.nith ) DcllADLDetection058,
    (SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5014 and BM_TH_S.nith = th.nith ) DcllADLConsequence059, 
    (SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4992 and BM_TH_S.nith = th.nith ) DclModeDeVie060,
    decode((SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4956 and BM_TH_S.nith = th.nith ),'','SI',(SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4956 and BM_TH_S.nith = th.nith )) DclAPA061,
    decode((SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4955 and BM_TH_S.nith = th.nith ),'','SI',(SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4955 and BM_TH_S.nith = th.nith )) DclALD062,
    decode((SELECT decode(BM_REP_S.REPONSE,'aucune','AUCUNE','tutelle','TUTELLE','curatelle','CURATELLE','sans information','SI') FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4990 and BM_TH_S.nith = th.nith ),'','SI',(SELECT decode(BM_REP_S.REPONSE,'aucune','AUCUNE','tutelle','TUTELLE','curatelle','CURATELLE','sans information','SI') FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4990 and BM_TH_S.nith = th.nith )) DclMesureProtection063,
    (SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6033 and BM_TH_S.nith = th.nith ) DclFinSuivi064,
    (SELECT substr(BM_REP_S.REPONSE,0,2)||substr(BM_REP_S.REPONSE,4,2)||substr(BM_REP_S.REPONSE,7,4) FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6034 and BM_TH_S.nith = th.nith ) DclDateFinSuivi065,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Sans aucun traitement pharmaceutique' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAucun068, 
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Inhibiteurs de l''acétylcholiestérase' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPInhibAcetyl069,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Antagonistes des récepteurs NMDA' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAntagonisteNMDA070,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Antidépresseurs' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAntidepresseurs071,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Neuroleptiques' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPNeuroleptiques072,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Anxiolitiques' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAnxiolitiques073,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Hypnotiques' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPHypnotiques074,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Nootropes' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPNootropes075,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Autres traitements pharmacologiques chroniques' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAutres076,
    decode((SELECT BM_REP_S.REPONSE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 5035 and bm_rep_s.reponse='Patient traité par aucun des traitements listés' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclTPAucunDansListe077,
    (SELECT decode(BM_REP_S.REPONSE,'Non','N','Oui','O','SR') FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4967 and  BM_TH_S.nith = th.nith ) DclEffetIndesirable078,
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Aucune prise en charge' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRAucun079,
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un orthophoniste' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSROrthophoniste080, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un psychologue' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRPsychologue081,
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un kinésithérapeute' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRKinesitherapeute082, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un ergothérapeute' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRErgotherapeute083, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un groupe d''intervenants' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRGroupe084, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par un accueil de jour' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRAccueilJour085, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par MAIA' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRMaia086, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par CLIC' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRClic087, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par une plateforme de répits' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRRepits088, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Par SSIAD' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRSiad089, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4997 and bm_rep_s.reponse='Autre type de prise en charge' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclIPSRAutre090, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='Aucun' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCAucun091,
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='IRM' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCIRM092, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='Scanner' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCScanner093, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='SPECT' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCSPECT094, 
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='PET' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCPET095,
    decode((SELECT BM_REP_S.REPONSE FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 4976 and bm_rep_s.reponse='Biomarqueurs' and BM_TH_S.nith = th.nith ),NULL,'N','O') DclPCBioMarqueurs096, 
    (SELECT decode(BM_REP_S.REPONSE,'Oui','O','Non','N') FROM  BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND  BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6032 and  BM_TH_S.nith = th.nith ) DclPR097, 
    (SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6029 and  BM_TH_S.nith = th.nith ) PatientProfession112, 
    (SELECT BM_REP_S.CODE FROM BM_TH_S, BM_LIB_S, BM_REP_S, C_QUESTION WHERE BM_TH_S.NITH = BM_LIB_S.NITH AND BM_LIB_S.NILIB = BM_REP_S.NILIB AND BM_LIB_S.NIQ = C_QUESTION.NIQ AND C_QUESTION.NIQ = 6030 and  BM_TH_S.nith = th.nith ) PatientAccompagnant113
FROM
    bm_th_s th, 
    patient pat left join infopat infpat on pat.nipatient = infpat.nipatient,
    ej_srv srv
 
WHERE
    th.niquest = '194'                                 AND  -- 
    th.retrait = 'F'                                   AND  -- 
    pat.nipatient = th.nipatient                       AND  -- 
    substr(dates, 0, 8) = to_char(sysdate, 'yyyymmdd') AND  -- Date du jour 
    th.niservice = srv.niservice                       AND
    srv.retrait = 'F'

il y a de la duplication de code, est-ce possible de simplifier cette requête ?
Merci.

**escartefigue** · 08/04/2015, 10h39

La requête est assez indigeste avec cette succession sans fin de DECODE, c'est quand même assez limite de faire autant de conversions de valeurs dans une requête SQL.
SQL a pour vocation première de récolter ou mettre à jour les données, pas de faire toute cette chirurgie esthétique, et à maintenir c'est l'enfer.

il faudrait aussi ne pas mélanger le filtrage (where) et les critères de jointure (inner join on...)

**SQLpro** · 08/04/2015, 11h17

Les américains disent "garbage in, garbage out". Cela se traduit par : si tu as de la merde en entrée, tu as de merde en sortie....

Ici votre modèle de données semble merdique. Il aurait sans doute fallut des tables de référence. D’où la merde en sortie des requêtes épouvantable à écrire et aux performances lamentables !

A +

**bstevy** · 08/04/2015, 11h55

C'est pas "shit in shit out" plutot ? moi, c'est plutôt ca que j'avais l'habitude d'entendre

et c'est plus rapide à dire

**CinePhil** · 08/04/2015, 13h30

C'est probablement simplifiable tellement cette requête est horrible mais bonjour le boulot !

Bon courage !

Début de simplification...

1) Commençons par récrire correctement les jointures principales et le début du SELECT avant la première sous-requête corrélée.

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
SELECT
    pat.nom NOM,
    pat.sexe SEXE,
    pat.nip ipp,
    SUBSTR(datenais, 1, 4) ANNEE_NAISSANCE,
    SUBSTR(datenais, 5, 2) MOIS_NAISSANCE,
    SUBSTR(datenais, 7, 2) JOUR_NAISSANCE,
    DECODE(pat.PATRONYME, null, pat.NOM, '', pat.NOM, pat.PATRONYME) NOM_NAISSANCE,
    pat.prenom PRENOM,
    infpat.NUMSECU NSS,
    DECODE(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 6, 2)) DEP_NAISSANCE,
    DECODE(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 8, 3)) VILLE_NAISSANCE,
    DECODE(srv.nietablis, 1, 'MZ', 3, 'MZ', 4, 'MZ', 5, 'MZ', 8, 'MZ', 9, 'MZ', 10, 'TH', 11, 'TH', 12, 'TH', 13, 'TH', 14, 'TH', 15, 'TH', 16, 'TH', 17, 'TH', 18, 'TH', 19, 'TH', 20, 'MZ', 21, 'TH', 22, 'MZ', 23, 'TH', 25, 'TH', 24, 'MZ', 'Autre') etb -- les sous-requêtes corrélées viennent ensuite,
FROM bm_th_s th 
INNER JOIN patient pat ON pat.nipatient = th.nipatient 
	LEFT OUTER JOIN infopat infpat ON pat.nipatient = infpat.nipatient
INNER JOIN ej_srv srv ON th.niservice = srv.niservice
WHERE th.niquest = '194'
	AND th.retrait = 'F'
	AND SUBSTR(dates, 0, 8) = TO_CHAR(SYSDATE, 'yyyymmdd')
	AND srv.retrait = 'F'

2) Les sous-requêtes corrélées semblent toutes de la même forme avec les mêmes jointures (je n'ai pas regarder en détail, c'est quand même à vérifier).
On peut donc passer le bloc FROM de ces sous-requêtes dans les jointures de la requête principale. On en profite pour mettre des alias afin de simplifier l'écriture.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
FROM bm_th_s th 
INNER JOIN patient pat ON pat.nipatient = th.nipatient 
	LEFT OUTER JOIN infopat infpat ON pat.nipatient = infpat.nipatient
INNER JOIN ej_srv srv ON th.niservice = srv.niservice
-- Jointures pour les sous-requêtes corrélées
INNER JOIN BM_TH_S bmt ON bmt.nith = th.nith
	INNER JOIN BM_LIB_S bml ON bml.NITH = bmt.NITH
		INNER JOIN BM_REP_S bmr ON bmr.NILIB = bml.NILIB
			INNER JOIN C_QUESTION q ON bml.NIQ = q.NIQ

3) On transforme la série de requêtes corrélées en série de structures CASE WHEN.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
	CASE
		WHEN q.NIQ = 4993 THEN bmr.code
	END AS PatientNiveauEtudes049,
	CASE
		WHEN q.NIQ = 5023 THEN bmr_code
	END AS PAtientEnvoyePar051
	-- etc. sur le même principe

Ce qui donne pour ce début de simplification la requête suivante :

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
SELECT
    pat.nom NOM,
    pat.sexe SEXE,
    pat.nip ipp,
    SUBSTR(datenais, 1, 4) ANNEE_NAISSANCE,
    SUBSTR(datenais, 5, 2) MOIS_NAISSANCE,
    SUBSTR(datenais, 7, 2) JOUR_NAISSANCE,
    DECODE(pat.PATRONYME, null, pat.NOM, '', pat.NOM, pat.PATRONYME) NOM_NAISSANCE,
    pat.prenom PRENOM,
    infpat.NUMSECU NSS,
    DECODE(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 6, 2)) DEP_NAISSANCE,
    DECODE(infpat.NUMSECU, null, '', '', '', substr(infpat.NUMSECU, 8, 3)) VILLE_NAISSANCE,
    DECODE(srv.nietablis, 1, 'MZ', 3, 'MZ', 4, 'MZ', 5, 'MZ', 8, 'MZ', 9, 'MZ', 10, 'TH', 11, 'TH', 12, 'TH', 13, 'TH', 14, 'TH', 15, 'TH', 16, 'TH', 17, 'TH', 18, 'TH', 19, 'TH', 20, 'MZ', 21, 'TH', 22, 'MZ', 23, 'TH', 25, 'TH', 24, 'MZ', 'Autre') etb,
	CASE
		WHEN q.NIQ = 4993 THEN bmr.code
	END AS PatientNiveauEtudes049,
	CASE
		WHEN q.NIQ = 5023 THEN bmr_code
	END AS PAtientEnvoyePar051
	-- etc. sur le même principe
FROM bm_th_s th 
INNER JOIN patient pat ON pat.nipatient = th.nipatient 
	LEFT OUTER JOIN infopat infpat ON pat.nipatient = infpat.nipatient
INNER JOIN ej_srv srv ON th.niservice = srv.niservice
-- Jointures pour les sous-requêtes corrélées
INNER JOIN BM_TH_S bmt ON bmt.nith = th.nith
	INNER JOIN BM_LIB_S bml ON bml.NITH = bmt.NITH
		INNER JOIN BM_REP_S bmr ON bmr.NILIB = bml.NILIB
			INNER JOIN C_QUESTION q ON bml.NIQ = q.NIQ
WHERE th.niquest = '194'
	AND th.retrait = 'F'
	AND SUBSTR(dates, 0, 8) = TO_CHAR(SYSDATE, 'yyyymmdd')
	AND srv.retrait = 'F'

À tester petit à petit, à chaque transformation, pour vérifier que le résultat obtenu est bien identique.

Encore une fois, bon courage !

**CliffeCSTL** · 10/04/2015, 16h01

Du peu de SQL que j'ai fait durant mes études, on m'a toujours appris à faire les jointures dans la clause WHERE, non ?

Simplifier requête SQL

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager