[SQL] Outer join récalcitrant

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

J'essaie de produire en SQL un espèce de tableau croisé dynamique, mais malgré mes outer join, il ne remplit que les lignes dont aucune colonne n'est nulle...

J'obtiens ceci :
Code:

1 2 3 4 5 6 7 TYPLIGFAC -------------------------------------------------------------------------------- MNT MNT MNT MNT MNT ---------- ---------- ---------- ---------- ---------- RR 100 200 300 4554 645
Alors que je voudrais également obtenir les lignes avec RO et RVAP.

J'ai créé un script SQL attaché pour que vous puissiez reproduire le problème.

Avez-vous une idée?

Merci d'avance.

Nicolas.

Code:

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
 
SQL> SELECT typligfac
  2       , prm.mnt
  3       , pb.mnt
  4       , rst.mnt
  5       , bcs.mnt
  6       , t.mnt
  7    FROM type_ligne_facture_nl typ
  8       , ligne_facture_nl lig
  9       , detail_ligne_facture_nl prm
 10       , detail_ligne_facture_nl pb
 11       , detail_ligne_facture_nl rst
 12       , detail_ligne_facture_nl bcs
 13       , detail_ligne_facture_nl t
 14    WHERE typ.idttypligfac = lig.idttypligfac
 15     AND prm.idtligfac(+) = lig.idtligfac
 16     AND pb.idtligfac(+) = lig.idtligfac
 17     AND rst.idtligfac(+) = lig.idtligfac
 18     AND bcs.idtligfac(+) = lig.idtligfac
 19     AND t.idtligfac(+) = lig.idtligfac
 20     AND prm.idtnarprm (+) = 1
 21     AND pb.idtnarprm (+) = 2
 22     AND rst.idtnarprm (+) = 3
 23     AND bcs.idtnarprm (+) = 4
 24     AND t.idtnarprm (+) = 5
TYPLIGFAC         MNT        MNT        MNT        MNT        MNT
---------- ---------- ---------- ---------- ---------- ----------
RR                100        200        300       4554        645
RO                 25        456         78
RVAP               78         18         89                 87787

09/05/2006, 10h48
Nico57

Laurent,

Merci pour cette réponse éclairée, rapide et juste :)

Nicolas.
09/05/2006, 11h04
laurentschneider

merci à toi d'avoir fourni le script qui crée les tables !

1 pièce(s) jointe(s)

Maintenant, j'ai un peu modifié le script, et je souhaiterais que soient affichées même les lignes vides. J'a modifié mes jointures, mais sans succès...

J'obtiens ceci :

Code:

1
2
3
4
5
6
7
 
IDTTYPLIGFAC     IDTFAC      PRIME         PB  RISTOURNE BACKSERVICE TRANSFERTIN
------------ ---------- ---------- ---------- ---------- ----------- -----------
           1          1        100        200        300        4554         645
           2          1         25        456         78
           3          1         78         18         89                   87787
           2          2                              769

Mais j'aimerais obtenir cela (ajouter des lignes pour TOUS les couples (idttypligfac, idtfac) qui n'existent pas ):

Code:

1
2
3
4
5
6
7
8
9
 
IDTTYPLIGFAC     IDTFAC      PRIME         PB  RISTOURNE BACKSERVICE TRANSFERTIN
------------ ---------- ---------- ---------- ---------- ----------- -----------
           1          1        100        200        300        4554         645
           2          1         25        456         78
           3          1         78         18         89                   87787
           1          2
           2          2                              769
           3          2

Je joins à nouveau le script de création.

Merci encore.

Nicolas.

[EDIT]

J'ai trouvé une solution, mais pas très èlégante... Y-a-t'il plus joli?

Code:

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
 
SELECT   typ.idttypligfac
       , lig.idtfac
       , prm.mnt prime
       , pb.mnt pb
       , rst.mnt ristourne
       , bcs.mnt backservice
       , t.mnt transfertin
    FROM type_ligne_facture_nl typ
       , ligne_facture_nl lig
       , detail_ligne_facture_nl prm
       , detail_ligne_facture_nl pb
       , detail_ligne_facture_nl rst
       , detail_ligne_facture_nl bcs
       , detail_ligne_facture_nl t
   WHERE typ.idttypligfac = lig.idttypligfac
     AND prm.idtligfac(+) = lig.idtligfac
     AND pb.idtligfac(+) = lig.idtligfac
     AND rst.idtligfac(+) = lig.idtligfac
     AND bcs.idtligfac(+) = lig.idtligfac
     AND t.idtligfac(+) = lig.idtligfac
     AND prm.idtnarprm(+) = 1
     AND pb.idtnarprm(+) = 2
     AND rst.idtnarprm(+) = 3
     AND bcs.idtnarprm(+) = 4
     AND t.idtnarprm(+) = 5
UNION
SELECT   idttypligfac
       , idtfac
       , NULL
       , NULL
       , NULL
       , NULL
       , NULL
    FROM facture_nl fac
       , type_ligne_facture_nl
   WHERE (idttypligfac, idtfac) NOT IN (
           SELECT typ.idttypligfac
                , lig.idtfac
             FROM type_ligne_facture_nl typ
                , ligne_facture_nl lig
                , detail_ligne_facture_nl prm
                , detail_ligne_facture_nl pb
                , detail_ligne_facture_nl rst
                , detail_ligne_facture_nl bcs
                , detail_ligne_facture_nl t
            WHERE typ.idttypligfac = lig.idttypligfac
              AND prm.idtligfac(+) = lig.idtligfac
              AND pb.idtligfac(+) = lig.idtligfac
              AND rst.idtligfac(+) = lig.idtligfac
              AND bcs.idtligfac(+) = lig.idtligfac
              AND t.idtligfac(+) = lig.idtligfac
              AND prm.idtnarprm(+) = 1
              AND pb.idtnarprm(+) = 2
              AND rst.idtnarprm(+) = 3
              AND bcs.idtnarprm(+) = 4
              AND t.idtnarprm(+) = 5)
ORDER BY idtfac
       , idttypligfac;

Essaye ceci !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SELECT typ.idttypligfac,
    fac.idtfac,
    sum(decode(dtl.idtnarprm,1,mnt)) prime,
    sum(decode(dtl.idtnarprm,2,mnt)) pb,
    sum(decode(dtl.idtnarprm,3,mnt)) ristourne,
    sum(decode(dtl.idtnarprm,4,mnt)) backservice,
    sum(decode(dtl.idtnarprm,5,mnt)) transfertin
FROM
        ( facture_nl fac cross join type_ligne_facture_nl typ )
        left join ligne_facture_nl lig on (typ.idttypligfac = lig.idttypligfac and fac.IDTFAC=lig.IDTFAC)
        left join detail_ligne_facture_nl dtl on (dtl.idtligfac = lig.idtligfac)
group by typ.idttypligfac, fac.idtfac
ORDER BY fac.idtfac, typ.idttypligfac
/
 
IDTTYPLIGFAC     IDTFAC      PRIME         PB  RISTOURNE BACKSERVICE TRANSFERTIN
------------ ---------- ---------- ---------- ---------- ----------- -----------
           1          1        100        200        300        4554         645
           2          1         25        456         78
           3          1         78         18         89                   87787
           1          2
           2          2                              769
           3          2

10/05/2006, 08h36
Nico57

Ca fonctionne en effet parfaitement :bravo:

J'ai encore du mal avec les Outer Join à la Oracle, j'étais plus habitué à ceux à la Sybase.

Par contre, juste une question comme ça : on se trouve dans un cas où on était obligés d'utiliser la "nouvelle" syntaxe "left join" etc... ou alors on aurait aussi pu le faire avec l'ancienne syntaxe "... = ... (+) " ?

En tout cas merci.

Nicolas.

la notion de "CROSS JOIN" n'existe pas dans Oracle 8i. Un cross join dans Oracle 8i, c'est simplement "pas de join". Or il n'est pas possible d'outerjoindre avec deux tables. Donc pour utiliser (+), il aurait fallut créer une sous requete.

Code:

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
 
SCOTT@LSC01> select * from ( facture_nl cross join type_ligne_facture_nl )
  2   left join ligne_facture_nl using (idttypligfac,IDTFAC) 
  3   order by idtfac,idttypligfac;
IDTTYPLIGFAC     IDTFAC NOMFAC          TYPLIGFAC   IDTLIGFAC
------------ ---------- --------------- ---------- ----------
           1          1 Facture NL      RR                  1
           2          1 Facture NL      RO                  2
           3          1 Facture NL      RVAP                3
           1          2 Facture Autre   RR
           2          2 Facture Autre   RO                  4
           3          2 Facture Autre   RVAP
 
6 rows selected.
 
SCOTT@LSC01> select * from facture_nl fac,type_ligne_facture_nl typ,ligne_facture_nl lig
  2  where typ.idttypligfac = lig.idttypligfac(+) and fac.IDTFAC=lig.IDTFAC(+);
where typ.idttypligfac = lig.idttypligfac(+) and fac.IDTFAC=lig.IDTFAC(+)
                       *
ERROR at line 2:
ORA-01417: a table may be outer joined to at most one other table
 
 
SCOTT@LSC01>  select * from  
  2   (select * from facture_nl,type_ligne_facture_nl) x,ligne_facture_nl lig 
  3   where x.idttypligfac = lig.idttypligfac(+) and x.IDTFAC=lig.IDTFAC(+) 
  4   order by 1,3;
    IDTFAC NOMFAC          IDTTYPLIGFAC TYPLIGFAC   IDTLIGFAC     IDTFAC IDTTYPLIGFAC
---------- --------------- ------------ ---------- ---------- ---------- ------------
         1 Facture NL                 1 RR                  1          1            1
         1 Facture NL                 2 RO                  2          1            2
         1 Facture NL                 3 RVAP                3          1            3
         2 Facture Autre              1 RR
         2 Facture Autre              2 RO                  4          2            2
         2 Facture Autre              3 RVAP

Clairement, la syntaxe CROSS JOIN est beaucoup plus efficace :D