Bonjour,

Voici mon problème. J'ai une première table dans laquelle j'ai une ligne différente pour chaque mois et je cherche à regrouper toutes les données sur une même ligne de ma table finale, qui contient donc une colonne pour la mesure pour chacun des 12 derniers mois.

Ma solution actuelle est de mettre toutes les données de la première table dans un cursor et de faire 12 updates dans la boucle dans laquelle je fetche le cursor, un pour chaque mois, avec à chaque fois la clause where permettant de ne faire l'update que si le record est du bon mois.

Je pense que si la condition du where était mise dans un if avant la commande d'update je pourrais gagner pas mal en temps d'exécution mais je ne parviens pas à avoir une syntaxe correcte. Sinon si vous avez d'autres idées elles sont les bienvenues.

Voici ma boucle dans l'état actuel (qui fonctionne mais avec tps d'exécution désastreux):

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
open subscription;
loop
FETCH subscription INTO subs_rec;
EXIT WHEN subscription%NOTFOUND;
 
 
update cmc_subscription_usage
set M = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And extract(Month from sysdate) = subs_rec.month
and extract(Year from sysdate) = subs_rec.year;
 
update cmc_subscription_usage
set M1 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 1 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 11 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M2 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 2 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 10 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M3 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 3 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 9 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M4 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 4 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 8 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M5 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 5 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 7 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M6 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 6 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 6 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M7 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 7 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 5 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M8 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 8 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 4 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M9 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 9 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 3 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M10 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 10 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 2 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M11 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
((extract(Month from sysdate) - 11 = subs_rec.month 
and extract(Year from sysdate) = subs_rec.year)
OR
(extract(Month from sysdate) + 1 = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year)) ;
 
update cmc_subscription_usage
set M12 = subs_rec.sessions
where subs_rec.nam_code = cmc_subscription_usage.nam_channel
And subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
and subs_rec.n_abon = cmc_subscription_usage.ide_subs
And 
(extract(Month from sysdate) = subs_rec.month 
and extract(Year from sysdate) - 1 = subs_rec.year) ;
 
 
END LOOP;
CLOSE subscription;