Pagination requête complexe

**nemo67** · 21/12/2009, 11h44

Bonjour

Grâce à Waldar j'ai réussi à écrire une requête qui correspond au résultat que j'attends.

Désormais je cherche à paginer cette requête de façon à minimiser le traffic réseau et optimiser les temps de chargement.

Pb je ne sais pas ou insérer le row_number car il doit se faire dans l'ordre du tri ???

De plus, la requête devient pour moi difficile à lire (et donc à maintenir) n'y aurait il pas un moyen de la rendre plus lisible ?

PS : les noms de colonnes sont génériques car la requête est construite dynamiquement ...

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
 
;WITH RES 
     AS (SELECT   T012_11.FAM + ' - ' + T012_11.LIB           AS col10, 
                  T012_12.FAM + ' - ' + T012_12.LIB           AS col11, 
                  ART.REFUN                                   AS col12, 
                  Sum(CASE 
                        WHEN Year(PIDT) = 2009 
                        THEN TOTALCA * SENS2 
                        ELSE 0 
                      END) AS col14, 
                  Grouping(T012_11.FAM + ' - ' + T012_11.LIB) AS col30, 
                  Grouping(T012_12.FAM + ' - ' + T012_12.LIB) AS col31, 
                  Grouping(ART.REFUN)                         AS col32 
         FROM     (SELECT MOUV.DOS, 
                          ENT.PIDT, 
                          MOUV.TIERS, 
                          MOUV.REF, 
                          MOUV.OP, 
                          MOUV.DEPO, 
                          ENT.REPR_0001, 
                          0                         AS TOTALQTE, 
                          MOUV.PUSTAT * MOUV.REFQTE AS TOTALCA, 
                          0                         AS TOTALMARGE1, 
                          0                         AS TOTALMARGE2, 
                          0                         AS TOTALMARGE3, 
                          (CASE 
                             WHEN T020.SENS = 1 
                             THEN -1 
                             ELSE 1 
                           END) AS SENS2, 
                          ENT.PINO                  AS PIECE 
                   FROM   MOUV 
                          JOIN ENT 
                            ON ENT.DOS = MOUV.DOS 
                               AND ENT.TICOD = 'C' 
                               AND ENT.PICOD = 4 
                               AND ENT.CE4 = 1 
                               AND ENT.PINO = MOUV.FANO 
                          JOIN T020 
                            ON T020.DOS = 999 
                               AND T020.CEBIN = 20 
                               AND T020.OP = MOUV.OP 
                          JOIN CLI 
                            ON ENT.DOS = CLI.DOS 
                               AND ENT.TIERS = CLI.TIERS 
                               AND CLI.CE1 = 3 
                   WHERE  ((ENT.PIDT >= { d '2009-01-01'} 
                            AND ENT.PIDT <= {d '2009-12-31'})) 
                          AND (ENT.DOS = 50 
                               AND ENT.CE4 = '1' 
                               AND ENT.TICOD = 'C' 
                               AND ENT.PICOD = 4 
                               AND MOUV.FACE4 = 1 
                               AND CLI.TIERS NOT IN ('C0200901','C0000172','C0200903','C0200904', 
                                                     'C0200920'))) AS BASE 
                  LEFT JOIN ART 
                    ON ART.DOS = BASE.DOS 
                       AND ART.REF = BASE.REF 
                  LEFT JOIN T012 AS T012_11 
                    ON T012_11.DOS = BASE.DOS 
                       AND T012_11.CEBIN = 12 
                       AND T012_11.FAMNO = 1 
                       AND T012_11.FAM = Left(ART.FAM_0001,1) 
                  LEFT JOIN T012 AS T012_12 
                    ON T012_12.DOS = BASE.DOS 
                       AND T012_12.CEBIN = 12 
                       AND T012_12.FAMNO = 1 
                       AND T012_12.FAM = Left(ART.FAM_0001,3) 
         GROUP BY T012_11.FAM + ' - ' + T012_11.LIB, 
                  T012_12.FAM + ' - ' + T012_12.LIB, 
                  ART.REFUN WITH ROLLUP) 
SELECT   *
FROM     RES 
ORDER BY col30, 
         Max(CASE 
               WHEN col32 = 1 
                    AND col31 = 1 
                    AND col30 = 0 
               THEN col14 
             END) 
           OVER(PARTITION BY col10 ) ASC, 
         CASE 
           WHEN col32 = 1 
                AND col31 = 1 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         Max(CASE 
               WHEN col32 = 1 
                    AND col31 = 0 
                    AND col30 = 0 
               THEN col14 
             END) 
           OVER(PARTITION BY col11 ) ASC, 
         CASE 
           WHEN col32 = 1 
                AND col31 = 0 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         CASE 
           WHEN col32 = 0 
                AND col31 = 0 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         col14 ASC

**SQLpro** · 21/12/2009, 21h06

Pour affihcer les lignes de 1 à 10 :

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
WITH RES 
     AS (SELECT   T012_11.FAM + ' - ' + T012_11.LIB           AS col10, 
                  T012_12.FAM + ' - ' + T012_12.LIB           AS col11, 
                  ART.REFUN                                   AS col12, 
                  Sum(CASE 
                        WHEN Year(PIDT) = 2009 
                        THEN TOTALCA * SENS2 
                        ELSE 0 
                      END) AS col14, 
                  Grouping(T012_11.FAM + ' - ' + T012_11.LIB) AS col30, 
                  Grouping(T012_12.FAM + ' - ' + T012_12.LIB) AS col31, 
                  Grouping(ART.REFUN)                         AS col32 
         FROM     (SELECT MOUV.DOS, 
                          ENT.PIDT, 
                          MOUV.TIERS, 
                          MOUV.REF, 
                          MOUV.OP, 
                          MOUV.DEPO, 
                          ENT.REPR_0001, 
                          0                         AS TOTALQTE, 
                          MOUV.PUSTAT * MOUV.REFQTE AS TOTALCA, 
                          0                         AS TOTALMARGE1, 
                          0                         AS TOTALMARGE2, 
                          0                         AS TOTALMARGE3, 
                          (CASE 
                             WHEN T020.SENS = 1 
                             THEN -1 
                             ELSE 1 
                           END) AS SENS2, 
                          ENT.PINO                  AS PIECE 
                   FROM   MOUV 
                          JOIN ENT 
                            ON ENT.DOS = MOUV.DOS 
                               AND ENT.TICOD = 'C' 
                               AND ENT.PICOD = 4 
                               AND ENT.CE4 = 1 
                               AND ENT.PINO = MOUV.FANO 
                          JOIN T020 
                            ON T020.DOS = 999 
                               AND T020.CEBIN = 20 
                               AND T020.OP = MOUV.OP 
                          JOIN CLI 
                            ON ENT.DOS = CLI.DOS 
                               AND ENT.TIERS = CLI.TIERS 
                               AND CLI.CE1 = 3 
                   WHERE  ((ENT.PIDT >= { d '2009-01-01'} 
                            AND ENT.PIDT <= {d '2009-12-31'})) 
                          AND (ENT.DOS = 50 
                               AND ENT.CE4 = '1' 
                               AND ENT.TICOD = 'C' 
                               AND ENT.PICOD = 4 
                               AND MOUV.FACE4 = 1 
                               AND CLI.TIERS NOT IN ('C0200901','C0000172','C0200903','C0200904', 
                                                     'C0200920'))) AS BASE 
                  LEFT JOIN ART 
                    ON ART.DOS = BASE.DOS 
                       AND ART.REF = BASE.REF 
                  LEFT JOIN T012 AS T012_11 
                    ON T012_11.DOS = BASE.DOS 
                       AND T012_11.CEBIN = 12 
                       AND T012_11.FAMNO = 1 
                       AND T012_11.FAM = LEFT(ART.FAM_0001,1) 
                  LEFT JOIN T012 AS T012_12 
                    ON T012_12.DOS = BASE.DOS 
                       AND T012_12.CEBIN = 12 
                       AND T012_12.FAMNO = 1 
                       AND T012_12.FAM = LEFT(ART.FAM_0001,3) 
         GROUP BY T012_11.FAM + ' - ' + T012_11.LIB, 
                  T012_12.FAM + ' - ' + T012_12.LIB, 
                  ART.REFUN WITH ROLLUP),
T2 AS
(                  
SELECT   *, ROW_NUMBER() OVER(ORDER BY col30, 
         Max(CASE 
               WHEN col32 = 1 
                    AND col31 = 1 
                    AND col30 = 0 
               THEN col14 
             END) 
           OVER(PARTITION BY col10 ) ASC, 
         CASE 
           WHEN col32 = 1 
                AND col31 = 1 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         Max(CASE 
               WHEN col32 = 1 
                    AND col31 = 0 
                    AND col30 = 0 
               THEN col14 
             END) 
           OVER(PARTITION BY col11 ) ASC, 
         CASE 
           WHEN col32 = 1 
                AND col31 = 0 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         CASE 
           WHEN col32 = 0 
                AND col31 = 0 
                AND col30 = 0 
           THEN 0 
           ELSE 1 
         END DESC, 
         col14 ASC) AS N
 
FROM     RES 
)
SELECT *
FROM   T2
WHERE  N BETWEEN 1 AND 10

A lire sur les fonction fenêtrées : http://sqlpro.developpez.com/article...clause-window/

A +

**nemo67** · 21/12/2009, 22h16

Merci SQLpro pour votre réponse

Par contre désolé, cela ne fonctionne pas. J'ai l'erreur suivante :

Windowed functions cannot be used in the context of another windowed function or aggregate.

Je ne comprends pas ? je ne pourrai pas utiliser de fonctions de pagination sur des aggregats ? je ne sais plus du tout ou chercher ...

Pour la simplification, avez vous une idée ? j'avoue que pour la maintenance cela me fait très peur !

**Waldar** · 22/12/2009, 11h51

Vous ne pouvez pas imbriquer une fonction de fenêtrage (le MAX() OVER()) dans une autre (le ROW_NUMBER).

Il vous faut passer par une sous-requête supplémentaire.
J'en profite pour sortir votre sous-requête BASE dans une CTE :

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
WITH BASE (DOS, PIDT, TIERS, REF, OP, DEPO, REPR_0001, TOTALQTE,
           TOTALCA, TOTALMARGE1, TOTALMARGE2, TOTALMARGE3, SENS2, PIECE) AS
(
SELECT MOUV.DOS, 
       ENT.PIDT, 
       MOUV.TIERS, 
       MOUV.REF, 
       MOUV.OP, 
       MOUV.DEPO, 
       ENT.REPR_0001, 
       0                         AS TOTALQTE, 
       MOUV.PUSTAT * MOUV.REFQTE AS TOTALCA, 
       0                         AS TOTALMARGE1, 
       0                         AS TOTALMARGE2, 
       0                         AS TOTALMARGE3, 
       (CASE 
         WHEN T020.SENS = 1 
         THEN -1 
         ELSE 1 
       END) AS SENS2, 
       ENT.PINO                  AS PIECE 
  FROM MOUV 
       INNER JOIN ENT 
         ON ENT.DOS = MOUV.DOS 
        AND ENT.TICOD = 'C' 
        AND ENT.PICOD = 4 
        AND ENT.CE4 = 1 
        AND ENT.PINO = MOUV.FANO 
       INNER JOIN T020 
         ON T020.DOS = 999 
        AND T020.CEBIN = 20 
        AND T020.OP = MOUV.OP 
       INNER JOIN CLI 
         ON ENT.DOS = CLI.DOS 
        AND ENT.TIERS = CLI.TIERS 
        AND CLI.CE1 = 3 
 WHERE ENT.PIDT >= {d '2009-01-01'} 
   AND ENT.PIDT <= {d '2009-12-31'}
   AND ENT.DOS = 50 
   AND ENT.CE4 = '1' 
   AND ENT.TICOD = 'C' 
   AND ENT.PICOD = 4 
   AND MOUV.FACE4 = 1 
   AND CLI.TIERS NOT IN ('C0200901', 'C0000172', 'C0200903', 'C0200904', 'C0200920')
)
  , RES1 (col10, col11, col12, col14, col30, col31, col32) AS
(
SELECT   T012_11.FAM + ' - ' + T012_11.LIB           AS col10, 
         T012_12.FAM + ' - ' + T012_12.LIB           AS col11, 
         ART.REFUN                                   AS col12, 
         Sum(CASE 
               WHEN Year(PIDT) = 2009 
               THEN TOTALCA * SENS2 
               ELSE 0 
             END) AS col14, 
         Grouping(T012_11.FAM + ' - ' + T012_11.LIB) AS col30, 
         Grouping(T012_12.FAM + ' - ' + T012_12.LIB) AS col31, 
         Grouping(ART.REFUN)                         AS col32
FROM     BASE 
         LEFT JOIN ART 
           ON ART.DOS = BASE.DOS 
          AND ART.REF = BASE.REF 
         LEFT JOIN T012 AS T012_11 
           ON T012_11.DOS = BASE.DOS 
          AND T012_11.CEBIN = 12 
          AND T012_11.FAMNO = 1 
          AND T012_11.FAM = LEFT(ART.FAM_0001,1) 
         LEFT JOIN T012 AS T012_12 
           ON T012_12.DOS = BASE.DOS 
          AND T012_12.CEBIN = 12 
          AND T012_12.FAMNO = 1 
          AND T012_12.FAM = LEFT(ART.FAM_0001,3) 
GROUP BY T012_11.FAM + ' - ' + T012_11.LIB, 
         T012_12.FAM + ' - ' + T012_12.LIB, 
         ART.REFUN WITH ROLLUP
)
  ,  RES2 ( col10, col11, col12, col14, col30,
            ord1 , ord2 , ord3 , ord4 , ord5 ) AS
(
SELECT   col10, col11, col12, col14, col30,
         Max(CASE 
               WHEN col32 = 1 
                AND col31 = 1 
                AND col30 = 0 
               THEN col14 
             END) OVER(PARTITION BY col10) AS ord1, 
         CASE 
           WHEN col32 = 1 
            AND col31 = 1 
            AND col30 = 0 
           THEN 0 ELSE 1 
         END                               AS ord2, 
         Max(CASE 
               WHEN col32 = 1 
                AND col31 = 0 
                AND col30 = 0 
               THEN col14 
             END) OVER(PARTITION BY col11) AS ord3, 
         CASE 
           WHEN col32 = 1 
            AND col31 = 0 
            AND col30 = 0 
           THEN 0 ELSE 1 
         END                               AS ord4, 
         CASE 
           WHEN col32 = 0 
            AND col31 = 0 
            AND col30 = 0 
           THEN 0 ELSE 1 
         END                               AS ord5
FROM     RES1
)
  ,  RES3 ( col10, col11, col12, col14, rn ) AS
(
SELECT   col10, col11, col12, col14,
         ROW_NUMBER() OVER(ORDER BY col30 ASC,
                                    ord1  ASC,
                                    ord2 DESC,
                                    ord3  ASC,
                                    ord4 DESC,
                                    ord5 DESC,
                                    col14 ASC)
FROM     RES2
)
SELECT col10, col11, col12, col14
  FROM RES3
 WHERE rn BETWEEN 1 AND 10;

**nemo67** · 22/12/2009, 22h21

Effectivement Waldar cela fonctionne, par contre ca rame rame ... beaucoup plus que s'il n'y avait pas de pagination !

Du coup je suis perplexe quant à l'utilité de la pagination. Qu'en pensez vous ?

Pagination requête complexe

Développement SQL Server

Discussions similaires

Partager

Partager