comment rendre une Requête récursive une vue ou PS ?

**Just-Soft** · 30/12/2012, 10h24

Bonjour
voilà tout a été dit dans la question.
Ma requête fait la travail demandé (tant mieux) mais là je voudrais la rendre vue ou PS pour l'insérer dans une autre PS qui se chargera de l'exécution mais à ce niveau là je bloque car tout mes essais n'ont pas aboutis.

Pour l'éclaircissement voici la requête en question :

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
with Simul_TARIF (Ouvrage, Date_Bilan, CTRF, PMA, PMD, F_HT, E41, E42, E43)
        -- définitions des variables de la CTE --
        --STEP:        -- station d'épuration
        --SR:          -- station de relevage
        --CTRF:        -- code tarif
        --PMA:         -- puissance maximale atteinte
        --PMD:         -- puissance mise à disposition
        --F_HT:        -- montant de la facture en HT
        --E41:         -- simulation avec le tarif E41
        --E42:         -- simulation avec le tarif E42
        --E43:         -- simulation avec le tarif E43
 
as
    (select distinct
        -- Ouvrage soit une STEP ou une SR
        case
            when d.nom_step is null then
                'SR ' || e.nom_sr else
                'STEP ' || d.nom_step
        end Ouvrage,
        a.date_mois,
        c.code_tarif,
        a.pma,
        b.pmd,
        a.montant_facture_ht,
 
        -- E41
        case
            /* si appreciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 7.2668)
            + (a.cadran2 * 1.6147 )
            + (a.cadran3 * 0.8533)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + (coalesce(a.kvarh,0) * 0.07588)
            + 32227.79
 
            else (a.cadran1 * 7.2668)
            + (a.cadran2 * 1.6147)
            + (a.cadran3 * 0.8533)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + 32227.79
        end E41,
 
        -- E42
        case
            /* si appreciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            +(coalesce(a.kvarh,0)* 0.3794) + 429.71
 
            else (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            + 429.71
        end E42,
 
        -- E43
        case
            /* si appreciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            +(coalesce(a.kvarh,0) * 0.3794) + 429.71
 
            else (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) *128.80 )
            + 429.71
        end E43
 
        from tb_energie a
            inner join tb_contrat b on (b.num_client = a.num_client)
            inner join tb_tarif c on (c.code_tarif = b.code_tarif)
            left join tb_step d on (d.code_step = a.code_step)
            left join tb_sr e on (e.code_sr = a.code_sr)
            inner join tb_unite f on (f.code_unite = d.code_unite)
                or (f.code_unite = e.code_unite)
 
        where (a.date_mois between :date_debut and :date_fin)
 
        order by f.code_unite, d.code_step, e.code_sr, a.date_mois
    )
 
    Select 
        Ouvrage,            -- station d'épuration ou station de relevage
        max(CTRF) TARIF,    -- tarif de base
        count(*) nb_fois,   -- nombre de fois où le tarif est avantageux
        avg(E41) E41,                -- simulation avec le tarif 41
        avg(E42) E42,                -- simulation avec le tarif 42
        avg(E43) E43,                -- simulation avec le tarif 43
        -- recherche du tarif le plus avantageux
        -- avec affichage du code tarif
        case
            -- si le tarif n'est pas dans la gamme E4? alors afficher '/'
            when (not(CTRF in ('E41','E42','E43')))
                then '/'
 
            -- si l'un des tarifs est nul alors arrêter la comparaison
            -- et afficher '/'
            when ((E41 is null) or (E42 is null) or (E43 is null))
                then '/'
 
            ----------------------------------------------------------------
            -- algorithme de comparaison soit a, b, c des valeurs non nuls
            ----------------------------------------------------------------
            /* si le résultat de (si( a > b alors
                                  écrire b sinon écrire a )
                                  > c) alors écrire c
               sinon (si(a > b alors écrire b sinon écrire a) ) */
            ----------------------------------------------------------------
            -- fin algorithme
            ----------------------------------------------------------------
            when (
                    case
                        when (E41 > E42) then 42 else 41
                    end
                 ) > E43 then 43
                    else (
                        case
                            when (E41 > E42) then 42 else 41
                        end
                        )
        end TR_MIN
 
    From Simul_TARIF
 
    group by Ouvrage, TR_MIN
 
    order by Ouvrage,TR_MIN desc

Merci de me lire et 1000 fois merci pour celui qui m'aidera à m'en sortir.

**Ph. B.** · 30/12/2012, 22h57

Bonjour,
Tout d’abord, cette expression de table commune (CTE) n'est en rien récursive

. Elle ne se corrèle pas, elle s'apparente donc plus à une vue...

Envoyé par Just-Soft

Ma requête fait la travail demandé (tant mieux)

Dans le cas présent, la clause Order by dans la CTE est inutile, hormis augmenter le temps de traitement ...

Envoyé par Just-Soft

mais là je voudrais la rendre vue ou PS pour l'insérer dans une autre PS qui se chargera de l'exécution mais à ce niveau là je bloque car tout mes essais n'ont pas aboutis.

Une CTE est assimilable à une requête de sélection.
Dans le corps d'une procédure stockée sélectionnable, on peut écrire :

Code :

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

1
2
3
4
5
6
7
8
...
FOR 
   WITH MA_CTE(...) AS (...)
   SELECT ... FROM MA_CTE WHERE ....
   INTO ...
DO
  SUSPEND;
...

Pour une vue, on pourrait écrire (je dis pourrait car créer une vue de ce qui est déjà une vue ne me parait pas pertinent) :

Code :

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

1
2
3
CREATE VIEW MA_VUE(COL1, COL2, ..., COLN)
   WITH MA_CTE(COL1, COL2, ..., COLN) AS (...)
   SELECT ... FROM MA_CTE;

**Just-Soft** · 31/12/2012, 12h27

Salut !
merci Ph. B. pour la réponse mais je ne me suis pas encore sortie.
tout d'abord pour la récursivité oui j'avoue que la mienne n'a rien de récursive mais s'était juste pour la mettre en oeuvre en la distinguant de la requête conventionnelle.

J'ai essayé avec la syntaxe vue mais j'ai eu une erreur à la 1ière ligne, idem pour la syntaxe procédure.

Je fais un copier/coller du code en espérant une correction :

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
185
186
CREATE VIEW SIMULATION(Ouvrage, Date_Bilan, CTRF, PMA, PMD, F_HT, E41, E42, E43, E44)
 
with Simul_TARIF (Ouvrage, Date_Bilan, CTRF, PMA, PMD, F_HT, E41, E42, E43, E44)
        -- définitions des variables de la CTE --
        --STEP:        -- station d'épuration
        --SR:          -- station de relevage
        --CTRF:        -- code tarif
        --PMA:         -- puissance maximale atteinte
        --PMD:         -- puissance mise à disposition
        --F_HT:        -- montant de la facture en HT
        --E41:         -- simulation avec le tarif E41
        --E42:         -- simulation avec le tarif E42
        --E43:         -- simulation avec le tarif E43
 
as
    (select distinct
        -- Ouvrage soit une STEP ou une SR
        case
            when d.nom_step is null then
                'SR ' || e.nom_sr else
                'STEP ' || d.nom_step
        end Ouvrage,
        a.date_mois,
        c.code_tarif,
        a.pma,
        b.pmd,
        a.montant_facture_ht,
 
        -- E41
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 0.8533)
            + (a.cadran2 * 7.2668 )
            + (a.cadran3 * 1.6147)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + (coalesce(a.kvarh,0) * 0.07588)
            + 32227.79
 
            else (a.cadran1 * 0.8533)
            + (a.cadran2 * 7.2668)
            + (a.cadran3 * 1.6147)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + 32227.79
        end E41,
 
        -- E42
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            +(coalesce(a.kvarh,0)* 0.3794) + 429.71
 
            else (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            + 429.71
        end E42,
 
        -- E43
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            +(coalesce(a.kvarh,0) * 0.3794) + 429.71
 
            else (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) *128.80 )
            + 429.71
        end E43,
 
        -- E44
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.ENERGIE_ACTIVE * 0.1548)
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            +(coalesce(a.kvarh,0) * 0.3794) + 429.71
 
            else (a.ENERGIE_ACTIVE * 0.1548)
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            + 429.71
        end E44
 
        from tb_energie a
            inner join tb_contrat b on (b.num_client = a.num_client)
            inner join tb_tarif c on (c.code_tarif = b.code_tarif)
            left join tb_step d on (d.code_step = a.code_step)
            left join tb_sr e on (e.code_sr = a.code_sr)
            inner join tb_unite f on (f.code_unite = d.code_unite)
                or (f.code_unite = e.code_unite)
 
        where (a.date_mois between :date_debut and :date_fin)
 
    )
 
    Select 
        Ouvrage,                -- station d'épuration ou station de relevage
        Date_Bilan,
        max(CTRF) TARIF,        -- tarif de base
        sum(F_HT) MONTNAT_HT,   -- montant HT
        count(*) nb_fois,       -- nombre de fois où le tarif est avantageux
        avg(E41) E41,           -- simulation avec le tarif 41
        avg(E42) E42,           -- simulation avec le tarif 42
        avg(E43) E43,           -- simulation avec le tarif 43
        avg(E44) E44,           -- simulation avec le tarif 44
 
        -- recherche du tarif le plus avantageux
        -- avec affichage du code tarif
        case
            -- si le tarif n'est pas dans la gamme E4? alors afficher '/'
            when (not(CTRF in ('E41','E42','E43')))
                then '/'
 
            -- si l'un des tarifs est nul alors arrêter la comparaison
            -- et afficher '/'
            when ((E41 is null) or (E42 is null) or (E43 is null))
                then '/'
 
            ----------------------------------------------------------------
            -- algorrithme de comparaison soit a, b, c des valeurs non nuls
            ----------------------------------------------------------------
            /* si le résultat de (si( a > b alors
                                  écrire b sinon écrire a )
                                  > c) alors écrire c
               sinon (si(a > b alors écrire b sinon écrire a) ) */
            ----------------------------------------------------------------
            -- fin algorithme
            ----------------------------------------------------------------
            when (
                    case
                        when (E41 > E42) then 42 else 41
                    end
                 ) > E43 then 43
                    else (
                        case
                            when (E41 > E42) then 42 else 41
                        end
                        )
        end TR_MIN,
 
        -- affichage du gain
        case
            -- si le tarif n'est pas dans la gamme E4? alors afficher '/'
            when (not(CTRF in ('E41','E42','E43')))
                then '/'
 
            -- si l'un des tarifs est nul alors arrêter la comparaison
            -- et afficher '/'
            when ((E41 is null) or (E42 is null) or (E43 is null))
                then '/'
 
            ----------------------------------------------------------------
            -- algorrithme de comparaison soit a, b, c des valeurs non nuls
            ----------------------------------------------------------------
            /* si le résultat de (si( a > b alors
                                  écrire b sinon écrire a )
                                  > c) alors écrire c
               sinon (si(a > b alors écrire b sinon écrire a) ) */
            ----------------------------------------------------------------
            -- fin algorithme
            ----------------------------------------------------------------
            when (
                    case
                        when (E41 > E42) then cast((F_HT-E42) as decimal(15,2))
                            else cast((F_HT-E41) as decimal(15,2))
                    end
                 ) > E43 then cast((F_HT-E43) as decimal(15,2))
                    else (
                        case
                            when (E41 > E42) then cast((F_HT-E42) as decimal(15,2))
                            else cast((F_HT-E41) as decimal(15,2))
                        end
                        )
        end TR_MIN_GAIN
 
    From Simul_TARIF
 
    group by Ouvrage, TR_MIN, TR_MIN_GAIN, Date_Bilan
 
    order by Ouvrage,TR_MIN desc

**Ph. B.** · 31/12/2012, 19h05

Envoyé par Just-Soft

J'ai essayé avec la syntaxe vue mais j'ai eu une erreur à la 1ière ligne, idem pour la syntaxe procédure.

Faut étudier la syntaxe de création d'une vue !

Il manque un AS avant le WITH

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
185
186
CREATE VIEW SIMULATION(Ouvrage, Date_Bilan, CTRF, PMA, PMD, F_HT, E41, E42, E43, E44)
AS
with Simul_TARIF (Ouvrage, Date_Bilan, CTRF, PMA, PMD, F_HT, E41, E42, E43, E44)
        -- définitions des variables de la CTE --
        --STEP:        -- station d'épuration
        --SR:          -- station de relevage
        --CTRF:        -- code tarif
        --PMA:         -- puissance maximale atteinte
        --PMD:         -- puissance mise à disposition
        --F_HT:        -- montant de la facture en HT
        --E41:         -- simulation avec le tarif E41
        --E42:         -- simulation avec le tarif E42
        --E43:         -- simulation avec le tarif E43

as
    (select distinct
        -- Ouvrage soit une STEP ou une SR
        case
            when d.nom_step is null then
                'SR ' || e.nom_sr else
                'STEP ' || d.nom_step
        end Ouvrage,
        a.date_mois,
        c.code_tarif,
        a.pma,
        b.pmd,
        a.montant_facture_ht,

        -- E41
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 0.8533)
            + (a.cadran2 * 7.2668 )
            + (a.cadran3 * 1.6147)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + (coalesce(a.kvarh,0) * 0.07588)
            + 32227.79

            else (a.cadran1 * 0.8533)
            + (a.cadran2 * 7.2668)
            + (a.cadran3 * 1.6147)
            + (b.pmd * 21.54 )+ (coalesce(a.pma,0) * 96.79)
            + 32227.79
        end E41,

        -- E42
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            +(coalesce(a.kvarh,0)* 0.3794) + 429.71

            else (a.cadran2 * 7.2668)
            + (a.cadran1 + a.cadran3) * 1.5053
            + (b.pmd* 32.25) +( coalesce(a.pma,0)* 150.48)
            + 429.71
        end E42,

        -- E43
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            +(coalesce(a.kvarh,0) * 0.3794) + 429.71
            
            else (a.cadran1 * 0.8533)
            + (a.cadran2 + a.cadran3) * 3.5692
            + (b.pmd * 32.25) +( coalesce(a.pma,0) *128.80 )
            + 429.71
        end E43,

        -- E44
        case
            /* si apréciation_consigne = Majoration */
            when (a.appreciation_consigne = 'Majoration') then
            (a.ENERGIE_ACTIVE * 0.1548)
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            +(coalesce(a.kvarh,0) * 0.3794) + 429.71
            
            else (a.ENERGIE_ACTIVE * 0.1548)
            + (b.pmd * 32.25) +( coalesce(a.pma,0) * 128.80 )
            + 429.71
        end E44

        from tb_energie a
            inner join tb_contrat b on (b.num_client = a.num_client)
            inner join tb_tarif c on (c.code_tarif = b.code_tarif)
            left join tb_step d on (d.code_step = a.code_step)
            left join tb_sr e on (e.code_sr = a.code_sr)
            inner join tb_unite f on (f.code_unite = d.code_unite)
                or (f.code_unite = e.code_unite)

        where (a.date_mois between :date_debut and :date_fin)

    )

    Select 
        Ouvrage,                -- station d'épuration ou station de relevage
        Date_Bilan,
        max(CTRF) TARIF,        -- tarif de base
        sum(F_HT) MONTNAT_HT,   -- montant HT
        count(*) nb_fois,       -- nombre de fois où le tarif est avantageux
        avg(E41) E41,           -- simulation avec le tarif 41
        avg(E42) E42,           -- simulation avec le tarif 42
        avg(E43) E43,           -- simulation avec le tarif 43
        avg(E44) E44,           -- simulation avec le tarif 44

        -- recherche du tarif le plus avantageux
        -- avec affichage du code tarif
        case
            -- si le tarif n'est pas dans la gamme E4? alors afficher '/'
            when (not(CTRF in ('E41','E42','E43')))
                then '/'

            -- si l'un des tarifs est nul alors arrêter la comparaison
            -- et afficher '/'
            when ((E41 is null) or (E42 is null) or (E43 is null))
                then '/'

            ----------------------------------------------------------------
            -- algorrithme de comparaison soit a, b, c des valeurs non nuls
            ----------------------------------------------------------------
            /* si le résultat de (si( a > b alors
                                  écrire b sinon écrire a )
                                  > c) alors écrire c
               sinon (si(a > b alors écrire b sinon écrire a) ) */
            ----------------------------------------------------------------
            -- fin algorithme
            ----------------------------------------------------------------
            when (
                    case
                        when (E41 > E42) then 42 else 41
                    end
                 ) > E43 then 43
                    else (
                        case
                            when (E41 > E42) then 42 else 41
                        end
                        )
        end TR_MIN,

        -- affichage du gain
        case
            -- si le tarif n'est pas dans la gamme E4? alors afficher '/'
            when (not(CTRF in ('E41','E42','E43')))
                then '/'

            -- si l'un des tarifs est nul alors arrêter la comparaison
            -- et afficher '/'
            when ((E41 is null) or (E42 is null) or (E43 is null))
                then '/'

            ----------------------------------------------------------------
            -- algorrithme de comparaison soit a, b, c des valeurs non nuls
            ----------------------------------------------------------------
            /* si le résultat de (si( a > b alors
                                  écrire b sinon écrire a )
                                  > c) alors écrire c
               sinon (si(a > b alors écrire b sinon écrire a) ) */
            ----------------------------------------------------------------
            -- fin algorithme
            ----------------------------------------------------------------
            when (
                    case
                        when (E41 > E42) then cast((F_HT-E42) as decimal(15,2))
                            else cast((F_HT-E41) as decimal(15,2))
                    end
                 ) > E43 then cast((F_HT-E43) as decimal(15,2))
                    else (
                        case
                            when (E41 > E42) then cast((F_HT-E42) as decimal(15,2))
                            else cast((F_HT-E41) as decimal(15,2))
                        end
                        )
        end TR_MIN_GAIN

    From Simul_TARIF

    group by Ouvrage, TR_MIN, TR_MIN_GAIN, Date_Bilan

    --order by Ouvrage,TR_MIN desc

J'enlèverais le order by, bien que cela ne soit pas bloquant, cela peut devenir pénalisant en terme de performance. Une vue doit être considérée comme une table, cad sans ordre; celui-ci sera introduit lors d'un select sur la vue.