Decode et group by

**Waldar** · 16/12/2011, 11h49

Du code Oracle de siècle dernier, encore du max de max, et mon premier conseil pas suivi !
Modernisons un peu :

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
  SELECT P.CD_PROD
       , DP.ID_DOSS
       , coalesce(P.CD_FOUR,' ') AS CD_FOUR
       , coalesce(P.CD_EDIT,' ') AS CD_EDIT
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(F.LB_FOUR)     ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_FOUR
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.DESI_PROD)   ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_PROD
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_PROD_COMP),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_PROD_COMP
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_AUTE)     ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_AUTEUR
       , coalesce(P.CAT_PROD2,' ') AS CD_CAT
       , P.CAT_PROD                AS CD_TYPE          
       , coalesce(P.CD_ISBN,' ')   AS CD_ISBN          
       , P.NB_PAGE                 AS NB_PAGES
       , MAX(case
               when DP.CD_DESIPIEC IS NULL
                 OR DP.CD_DESIPIEC <> 10
                 OR (DP.CD_DESIPIEC = 10 AND DP.LB_LIEN IS NULL)
               then NULL
               else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || DP.LB_LIEN
             end) AS IMG_PRINC
       , MAX(case
               when DP.CD_DESIPIEC IS NULL
                 OR DP.CD_DESIPIEC <> 11
                 OR (DP.CD_DESIPIEC = 11 AND DP.LB_LIEN IS NULL)
               then NULL
               else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || DP.LB_LIEN
             end) AS IMG_SECOND
       , MAX(case
               when DP.CD_DESIPIEC IS NULL
                 OR DP.CD_DESIPIEC <> 12
                 OR (DP.CD_DESIPIEC = 12 AND DP.LB_LIEN IS NULL)
               then NULL
               else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || DP.LB_LIEN
             end) AS IMG_LOGO_MARQ
       , MAX(case
               when DP.CD_DESIPIEC IS NULL
                 OR DP.CD_DESIPIEC <> 13
                 OR (DP.CD_DESIPIEC = 13 AND DP.LB_LIEN IS NULL)
               then NULL
               else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || DP.LB_LIEN
             end) AS IMG_DETAIL_PROD_1
       , MAX(case
               when DP.CD_DESIPIEC IS NULL
                 OR DP.CD_DESIPIEC <> 14
                 OR (DP.CD_DESIPIEC = 14 AND DP.LB_LIEN IS NULL)
               then NULL
               else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || DP.LB_LIEN
             end) AS IMG_DETAIL_PROD_2
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBCIAL)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_COMMERCIAL
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBACCR)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_ACCROCHE
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBDESC)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_DESCRIPTIF
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBTECH)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') AS LB_CARACT_TECH_REGL
       , P.MT_ECOP              AS ECOPART
       , coalesce(D.NM_LONG, 0) AS LONGUEUR
       , coalesce(D.NM_LARG, 0) AS LARGEUR
       , coalesce(D.NM_HAUT, 0) AS HAUTEUR
       , coalesce(D.NM_POID, 0) AS POIDS
       , D.NM_STOCDISPO         AS STOCK_INDISPO_WEB
    FROM PRODUIT P
         LEFT OUTER JOIN FOURNISSEUR F
           ON F.CD_FOUR = P.CD_FOUR
         INNER JOIN ASSO_OFFRE_PROD A
           ON A.CD_PROD = P.CD_PROD
         LEFT OUTER JOIN DOSSIER D
           ON D.CD_PROD = P.CD_PROD
         LEFT OUTER JOIN DOSSIER_PIECE DP
           ON DP.ID_DOSS = D.ID_DOSS
   WHERE P.CD_PROD IS NOT NULL 
     AND A.CAMP >= 79
GROUP BY P.CD_PROD
       , DP.ID_DOSS
       , coalesce(P.CD_FOUR,' ')
       , coalesce(P.CD_EDIT,' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(F.LB_FOUR)     ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.DESI_PROD)   ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_PROD_COMP),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_AUTE)     ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(P.CAT_PROD2,' ')
       , P.CAT_PROD          
       , coalesce(P.CD_ISBN,' ')          
       , P.NB_PAGE
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBCIAL)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBACCR)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBDESC)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , coalesce(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBTECH)  ,CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ')
       , P.MT_ECOP
       , coalesce(D.NM_LONG, 0)
       , coalesce(D.NM_LARG, 0)
       , coalesce(D.NM_HAUT, 0)
       , coalesce(D.NM_POID, 0)
       , D.NM_STOCDISPO;