Regrouper 3 lignes en provenance de 2 tables [Résolu]

[Jamic]

Bonjour.

J'ai une table CONTRATS qui a les champs ID_CONTRAT, ID_GARANTIE et TAUX (de la garantie). Cette table contient une ligne par contrat.

J'ai aussi une table CONTRAT_GARANTIE qui a les champs ID_CONTRAT, ID_GARANTIE et MONTANT. Cette table peut contenir une ou deux garanties (donc une ou deux lignes) pour chaque contrat.

Je dois créer une requête qui, pour chaque contrat, me sort sur une même ligne l'Id du contrat et les infos de la ou des garanties de ce contrat.
Autrement dit, on doit se retrouver avec une ligne qui comporte les champs suivants : ID_CONTRAT, ID_GARANTIE_1, MONTANT_GARANTIE_1, TAUX_GARANTIE_1, ID_GARANTIE_2, MONTANT_GARANTIE_2, TAUX_GARANTIE_2.

Sachant que
- quand une garantie est présente dans CONTRAT_GARANTIE mais pas dans CONTRATS, son champ TAUXGARANTIE est affecté à zéro
- quand un contrat n'a qu'une seule garantie dans CONTRAT_GARANTIE, les champs de la deuxième garantie sont affectés à NULL


EXEMPLE :
Table CONTRATS :
Contrat1 --- Garantie1 --- Taux1
Contrat2 --- Garantie2 --- Taux2

Table CONTRAT_GARANTIE :
Contrat1 --- Garantie1 --- Montant1
Contrat2 --- Garantie2 --- Montant2
Contrat2 --- Garantie3 --- Montant3

Requête :
Contrat1 --- Garantie1 --- Montant1 --- Taux1 --- NULL --- NULL --- NULL
Contrat2 --- Garantie2 --- Montant2 --- Taux2 --- Garantie3 --- Montant3 --- 0



J'ai réussi à le faire grâce au code suivant mais il est beaucoup trop gourmand en ressources et en temps donc j'aimerais savoir s'il existe un moyen de faire la même chose mais beaucoup plus rapidement.
N.B. Le "ELSE NULL" est sous-entendu dans les CASE.

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
 
SELECT CNT_GAR.IDCONTRAT                    IDCONTRAT,
       GAR1                                 IDGARANTIE1,
       CONTRAT1.TAUX                        TAUX_1,
       GARANTIE1.MONTANT                    MONTANT_1,
       CASE WHEN GAR1 <> GAR2 THEN GAR2 END IDGARANTIE_2,
       CASE WHEN GAR1 <> GAR2 THEN 0    END TAUX_2,
       GARANTIE2.MONTANT                    MONTANT_2,
FROM
     (
       SELECT IDCONTRAT,
              MIN(IDGARANTIE) GAR1,
              MAX(IDGARANTIE) GAR2
       FROM CONTRAT_GARANTIE
       GROUP BY IDCONTRAT
     ) CNT_GAR
LEFT JOIN CONTRATS CONTRAT1
       ON (     CONTRAT1.IDCONTRAT  = CNT_GAR.IDCONTRAT
            AND CONTRAT1.IDGARANTIE = CNT_GAR.GAR1
          )
LEFT JOIN CONTRAT_GARANTIE GARANTIE1
       ON (     GARANTIE1.IDCONTRAT  = CNT_GAR.IDCONTRAT
            AND GARANTIE1.IDGARANTIE = CNT_GAR.GAR1
          )
LEFT JOIN CONTRAT_GARANTIE GARANTIE2
       ON (     GARANTIE2.IDCONTRAT  = CNT_GAR.IDCONTRAT
            AND GARANTIE2.IDGARANTIE = CNT_GAR.GAR2
          )
;

[McM]

quelle version de base ?

[Jamic]

9i

Mais c'est bon : j'ai pu résoudre mon problème en externalisant les jointures dans la requête appelante (celle que j'ai postée est en fait une sous-requête de cette requête appelante).

[McM]

Avec les fonctions analytiques, un seul accès aux tables.

[Jamic]

J'avais essayé la solution suivante mais ça dégradait encore plus les performances.

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
 
SELECT LISTE_CONTRATS.IDCONTRAT             IDCONTRAT,
       GARANTIE1.IDGARANTIE                 IDGARANTIE1,
       GARANTIE1.MONTANT                    MONTANT_1,
       GARANTIE1.TAUX                       TAUX_1,
       GARANTIE1.IDGARANTIE                 IDGARANTIE1,
       GARANTIE1.MONTANT                    MONTANT_1,
       CASE WHEN (GARANTIE2.GARANTIE IS NOT NULL) THEN 0 END TAUX_2
FROM ( SELECT DISTINCT IDCONTRAT
       FROM CONTRATS
     ) LISTE_CONTRATS
LEFT JOIN
          ( SELECT CG.IDCONTRAT, CG.IDGARANTIE, MONTANT, TAUX,
                   ROW_NUMBER() OVER (PARTITION BY CG.IDCONTRAT ORDER BY CG.IDCONTRAT) NUMERO_GARANTIE
            FROM CONTRAT_GARANTIE CG
            LEFT JOIN CONTRATS CONTRAT1
                   ON (     CONTRAT1.IDCONTRAT  = CG.IDCONTRAT
                        AND CONTRAT1.IDGARANTIE = CG.IDGARANTIE
                      )
          ) GARANTIE1
       ON (     GARANTIE1.IDCONTRAT = LISTE_CONTRATS.IDCONTRAT
            AND GARANTIE1.NUMERO_GARANTIE = 1
          )
LEFT JOIN
          ( SELECT CG.IDCONTRAT, CG.IDGARANTIE, MONTANT,
                   ROW_NUMBER() OVER (PARTITION BY CG.IDCONTRAT ORDER BY CG.IDCONTRAT) NUMERO_GARANTIE
            FROM CONTRAT_GARANTIE CG
          ) GARANTIE2
       ON (     GARANTIE2.IDCONTRAT = LISTE_CONTRATS.IDCONTRAT
            AND GARANTIE2.NUMERO_GARANTIE = 2
          )

[McM]

Avec les fonctions analytiques
(LEAD : On regarde les données de la ligne suivante si elles appartiennent au même contrat, ROW_NUMBER() : on récupère le n° de ligne par contrat )

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH contrat AS (
             SELECT 1 AS nocont, 'gar1' AS gar, 'taux1' AS taux FROM dual
    UNION ALL SELECT 2 AS nocont, 'gar2' AS gar, 'taux2' AS taux FROM dual),
contrat_garantie AS (
            SELECT 1 AS nocont, 'gar1' AS gar, 'mt1' AS mont FROM dual
    UNION ALL SELECT 2 AS nocont, 'gar2' AS gar, 'mt2' AS mont FROM dual
    UNION ALL SELECT 2 AS nocont, 'gar3' AS gar, 'mt3' AS mont FROM dual)
SELECT nocont, gar, mont, taux, garantie2, montant2,taux2
FROM (
    SELECT row_number() OVER(PARTITION BY c.nocont ORDER BY g.gar) AS num,
            c.nocont, c.gar, g.mont, c.taux, 
            LEAD(g.gar, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS garantie2,
            LEAD(g.mont, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS montant2,
            LEAD(0, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS taux2
    FROM contrat c, contrat_garantie g
    WHERE c.nocont = g.nocont
    ORDER BY c.nocont, g.gar        
    )
WHERE num = 1

Code :

1
2
3
NOCONT	GAR	MONT	TAUX	GARANTIE2	MONTANT2	TAUX2
1	gar1	mt1	taux1			
2	gar2	mt2	taux2	gar3	mt3	0

[Jamic]

Merci.

Finalement, on a pris une solution toute simple :

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
 
SELECT CNT.IDCONTRAT                        ID_CONTRAT,
       CNT.GARANTIE                         ID_GARANTIE_1,
       CNT.TAUX                             TAUX_1,
       GARANTIE1.MONTANT                    MONTANT_1,
       GARANTIE2.IDGARANTIE                 ID_GARANTIE_2,
       CASE WHEN GARANTIE2.IDGARANTIE IS NOT NULL THEN 0 END TAUX_2,
       GARANTIE2.MONTANT                    MONTANT_2
FROM 
     CONTRATS CNT
LEFT JOIN (
            SELECT IDCONTRAT,
                   IDGARANTIE,
                   MONTANT
            FROM CONTRAT_GARANTIE
          ) GARANTIE1
       ON (     CNT.IDCONTRAT  = GARANTIE1.IDCONTRAT
            AND CNT.IDGARANTIE = GARANTIE1.GARANTIE          
          )
LEFT JOIN (
            SELECT IDCONTRAT,
                   IDGARANTIE IDGARANTIE,
                   MONTANT
            FROM CONTRAT_GARANTIE
          ) GARANTIE2
       ON (     CNT.IDCONTRAT  =  GARANTIE2.IDCONTRAT
            AND CNT.IDGARANTIE <> GARANTIE2.GARANTIE          
          )
;