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