Demande d'aide pour une petite requete

**NounaM** · 18/06/2009, 18h51

Bonjour à tous,
je vous prie de m'aider sur un petit problème que j'ai rencontré au boulot.
j'ai la 1ere requete et qui est celle là:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT SUM(NVL(PRET.MONTANT,0)
FROM PRET
WHERE PRET.MATR = '00000000'
    AND PRET.RESTANT > 0 -- comme résultat elle me donne 0 qui est correct

une 2eme requete:

SELECT SUM(NVL(DEMANDE.MONTANT,0))
FROM DEMANDE, DIVERS
WHERE DEMANDE.ID_DEMANDE = DIVERS.ID_DEMANDE
    AND DIVERS.DECI = 'X'
    AND DIVERS.MATR = '00000000' -- dont le résultat est 1000 par exple

Et j'avais besoin d'une requete qui me donne la somme des deux résultat, la voilà:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT (SUM(NVL(PRET.MONTANT,0) + SUM(NVL(DEMANDE.MONTANT,0))) AS ENDET
FROM PRET, DEMANDE, DIVERS
WHERE DEMANDE.ID_DEMANDE = DIVERS.ID_DEMANDE
    AND PRET.MATR = DIVERS.MATR
    AND DIVERS.DECI = 'X'
    AND DIVERS.MATR = '00000000'     
    AND PRET.RESTANT > 0

sauf que cette dernière me donne 0 comme résultat alors qu'elle est supposée me retourner 1000. J'aimerai connaitre ce qui cloche dans cette dernière requete! Merci d'avance pr vos réponses

**Waldar** · 18/06/2009, 19h12

A votre place je le ferai ainsi :

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
SELECT
    COALESCE(PRT.MATR, DMD.MATR) AS MATR,
    COALESCE(PRT.MNT_PRT, 0) + COALESCE(DMD.MNT_DMD, 0) AS MNT_ENDET
FROM
    (
      SELECT PR.MATR,
             SUM(COALESCE(PR.MONTANT, 0)) AS MNT_PRT
        FROM PRET PR
       WHERE PR.RESTANT > 0
         AND PR.MATR = '00000000'
    GROUP BY PR.MATR
    ) PRT FULL OUTER JOIN
    (
      SELECT DI.MATR,
             SUM(COALESCE(DE.MONTANT, 0)) AS MNT_DMD
        FROM DEMANDE DE INNER JOIN DIVERS DI
          ON DE.ID_DEMANDE = DI.ID_DEMANDE 
       WHERE DI.DECI = 'X'
         AND DI.MATR = '00000000' -- dont le résultat est 1000 par exple
    GROUP BY DI.MATR
    ) DMD ON DMD.MATR = PRT.MATR

**NounaM** · 18/06/2009, 19h30

Merci pour la réponse sauf que ça ne marche pas, là ça me retourne aucun résultat alors que je suis supposée avoir 1000 comme résultat de la requete.

**Waldar** · 18/06/2009, 20h51

Si avec un FULL OUTER JOIN ça ne marche pas, je pense que vous devriez vérifier vos données, êtes-vous sûre que votre deuxième requête renvoie bien 1000 ?

Le problème, avec les nouvelles fonctionalités d'oracle, c'est qu'on va chercher bien loin des trucs bien bien simple.

Vous avez en gros posté la même chose que moi, et full outer join c'est loin de faire partie des nouvelles fonctionnalités.

**jmguiche** · 18/06/2009, 20h04

Le problème, avec les nouvelles fonctionalités d'oracle, c'est qu'on va chercher bien loin des trucs bien bien simple.

Chacune de tes requetes ramene UNE ligne avec UNE colone : il n'y a pas de risque de produit cartésien.
Tu veux les additionner ? Fait le :

Donc :

Select T1.A + T2.B
From
(SELECT SUM(NVL(PRET.MONTANT,0) A
FROM PRET
WHERE PRET.MATR = '00000000'
AND PRET.RESTANT > 0) T1,
(SELECT SUM(NVL(DEMANDE.MONTANT,0)) B
FROM DEMANDE, DIVERS
WHERE DEMANDE.ID_DEMANDE = DIVERS.ID_DEMANDE
AND DIVERS.DECI = 'X'
AND DIVERS.MATR = '00000000' ) T2

Si le problème est plus compliqué et que MATR est une clef de jointure entre tes 2 requetes :

Select T1.A + T2.B , T1.MATR
From
(SELECT SUM(NVL(PRET.MONTANT,0) A, Pret.MATR MATR
FROM PRET
WHERE -- PRET.MATR = '00000000'
PRET.RESTANT > 0) T1,
(SELECT SUM(NVL(DEMANDE.MONTANT,0)) B, Divers.MATR MATR
FROM DEMANDE, DIVERS
WHERE DEMANDE.ID_DEMANDE = DIVERS.ID_DEMANDE
AND DIVERS.DECI = 'X'
-- AND DIVERS.MATR = '00000000'
) T2
Where T1.MATR = T2.MATR
Group by T1.MATR

Mais si tu considère que ta requete doit ramener le bon résultat, c'est que tes données ne sont pas cohérentes.

**jmguiche** · 18/06/2009, 21h56

Envoyé par Waldar

Si avec un FULL OUTER JOIN ça ne marche pas, je pense que vous devriez vérifier vos données, êtes-vous sûre que votre deuxième requête renvoie bien 1000 ?

Vous avez en gros posté la même chose que moi, et full outer join c'est loin de faire partie des nouvelles fonctionnalités.

Je ne fait pas de jointure quand je met 2 lignes l'une en face de l'autre. Pas la peine de lancer l'algorithme de full outer et de plonger le pauvre gars qui maintient mon code dans un océan de perplexité.

Je ne fait pas un full outer join, mais un produit cartésien tout bête dans le premier cas (une ligne de chaque coté) et une jointure toute bête dans le second. Il est d'ailleurs possible que le full outer ramene trop de lignes si je l'avais utilisée dans ma requete no 2. Je pense que quand on utilise un FULL OUTER, c'est que fonctionnelement on en a besoin.

Je n'utilise pas de coalesce, ni de coalesce de sum(coalesce...). Je ne comprend d'ailleurs pas à quoi ça sert. Comment un sum(coalesce(...,0)) pourrait il être NULL ?

Votre pauvre lecteur doit bien se demander pourquoi il faut remplacer NVL par un double coalesce et pourquoi un full outer.

Si sa premiere requete répond bien 0 et la seconde 1000, je suis sûr que ma première requete fonctionne, à une éventuelle faute de frappe près.

Coalesce : version 9.
Full outer : version 9 ou plus, je ne sais plus.

Je n'utilise que des choses qui auraient pu être implémentées en V5 ou presque (en V5 il aurait fallu créer 2 vues parce qu'il n'y avait pas de vues dynamiques, mais les concepts étaient là.).

**Waldar** · 19/06/2009, 01h52

Je ne fait pas de jointure quand je met 2 lignes l'une en face de l'autre. Pas la peine de lancer l'algorithme de full outer et de plonger le pauvre gars qui maintient mon code dans un océan de perplexité....
...Il est d'ailleurs possible que le full outer ramene trop de lignes si je l'avais utilisée dans ma requete no 2

J'ai écrit la requête en prévoyant que celà devrait s'appliquer à un ensemble de matricules, ce ne sera peut-être pas le cas mais qui peut le plus peut le moins.

Je n'utilise pas de coalesce, ni de coalesce de sum(coalesce...). Je ne comprend d'ailleurs pas à quoi ça sert. Comment un sum(coalesce(...,0)) pourrait il être NULL ?

Pourquoi coalesce plutôt que nvl : parce que le premier prend n arguments, s'exécute plus vite, fait partie de la norme et fonctionne sur la majorité des SGBD.

Le sum(coalesce(...,0)) retournera un null s'il n'y a pas de lignes dans la table PRET par exemple.

Coalesce : version 9.
Full outer : version 9 ou plus, je ne sais plus.
Je n'utilise que des choses qui auraient pu être implémentées en V5 ou presque

8 ou 8i pour coalesce mais peu importe, je vous rappelle qu'Oracle ne supporte plus les versions antérieures à 9i et je ne vois vraiment pas le mal à utiliser ces outils.
Vous n'avez jamais utilisé les fonctions analytiques ? Si la réponse est non c'est dommage pour vous, c'est très puissant...

Votre seconde requête présente des erreurs au niveau des group by.

Quant aux full outer join, un petit jeu de test sera peut-être plus parlant qu'une explication :

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
CREATE table PRET (
    MATR       NUMBER(1) NOT NULL,
    MNT        NUMBER(18,8),
    constraint  PK_PRET primary key (MATR)
);
 
CREATE table DEMANDE (
    MATR       NUMBER(1) NOT NULL,
    MNT        NUMBER(18,8),
    constraint  PK_DEMANDE primary key (MATR)
);
 
 
INSERT INTO PRET VALUES (1, 1500);
INSERT INTO PRET VALUES (2, 2500);
INSERT INTO PRET VALUES (4, null);
INSERT INTO PRET VALUES (5, null);
INSERT INTO PRET VALUES (7, 7500);
INSERT INTO PRET VALUES (8, null);
 
INSERT INTO DEMANDE VALUES (1, 3000);
INSERT INTO DEMANDE VALUES (3, 5000);
INSERT INTO DEMANDE VALUES (4, null);
INSERT INTO DEMANDE VALUES (6, null);
INSERT INTO DEMANDE VALUES (7, null);
INSERT INTO DEMANDE VALUES (8, 17000);
 
commit;

Ma requête :

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
SELECT
    COALESCE(PRT.MATR, DMD.MATR) AS MATR,
    COALESCE(PRT.MNT_PRT, 0) + COALESCE(DMD.MNT_DMD, 0) AS MNT_ENDET
FROM
    (
      SELECT P.MATR,
             SUM(COALESCE(P.MNT, 0)) AS MNT_PRT
        FROM PRET P
    GROUP BY P.MATR
    ) PRT FULL OUTER JOIN
    (
      SELECT D.MATR,
             SUM(COALESCE(D.MNT, 0)) AS MNT_DMD
        FROM DEMANDE D
    GROUP BY D.MATR
    ) DMD ON DMD.MATR = PRT.MATR
ORDER BY 1 ASC;
 
MATR	MNT_ENDET
1	4500
2	2500
3	5000
4	0
5	0
6	0
7	7500
8	17000

Votre seconde requête (corrigée) :

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
SELECT
    T1.MATR,
    T1.A + T2.B MNT_ENDET
FROM
    ( SELECT P.MATR,
             SUM(NVL(P.MNT, 0)) A
        FROM PRET P
    GROUP BY P.MATR ) T1,
    ( SELECT D.MATR,
             SUM(NVL(D.MNT, 0)) B
        FROM DEMANDE D
    GROUP BY D.MATR ) T2
WHERE T1.MATR = T2.MATR
ORDER BY 1 ASC;
 
MATR	MNT_ENDET
1	4500
4	0
7	7500
8	17000

**jmguiche** · 19/06/2009, 08h34

Envoyé par Waldar

J'ai écrit la requête en prévoyant que celà devrait s'appliquer à un ensemble de matricules, ce ne sera peut-être pas le cas mais qui peut le plus peut le moins.

Pourquoi coalesce plutôt que nvl : parce que le premier prend n arguments, s'exécute plus vite, fait partie de la norme et fonctionne sur la majorité des SGBD.

Le sum(coalesce(...,0)) retournera un null s'il n'y a pas de lignes dans la table PRET par exemple.

8 ou 8i pour coalesce mais peu importe, je vous rappelle qu'Oracle ne supporte plus les versions antérieures à 9i et je ne vois vraiment pas le mal à utiliser ces outils.
Vous n'avez jamais utilisé les fonctions analytiques ? Si la réponse est non c'est dommage pour vous, c'est très puissant...

Votre seconde requête présente des erreurs au niveau des group by.

Quant aux full outer join, un petit jeu de test sera peut-être plus parlant qu'une explication :

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
CREATE table PRET (
    MATR       NUMBER(1) NOT NULL,
    MNT        NUMBER(18,8),
    constraint  PK_PRET primary key (MATR)
);
 
CREATE table DEMANDE (
    MATR       NUMBER(1) NOT NULL,
    MNT        NUMBER(18,8),
    constraint  PK_DEMANDE primary key (MATR)
);
 
 
INSERT INTO PRET VALUES (1, 1500);
INSERT INTO PRET VALUES (2, 2500);
INSERT INTO PRET VALUES (4, null);
INSERT INTO PRET VALUES (5, null);
INSERT INTO PRET VALUES (7, 7500);
INSERT INTO PRET VALUES (8, null);
 
INSERT INTO DEMANDE VALUES (1, 3000);
INSERT INTO DEMANDE VALUES (3, 5000);
INSERT INTO DEMANDE VALUES (4, null);
INSERT INTO DEMANDE VALUES (6, null);
INSERT INTO DEMANDE VALUES (7, null);
INSERT INTO DEMANDE VALUES (8, 17000);
 
commit;

Ma requête :

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
SELECT
    COALESCE(PRT.MATR, DMD.MATR) AS MATR,
    COALESCE(PRT.MNT_PRT, 0) + COALESCE(DMD.MNT_DMD, 0) AS MNT_ENDET
FROM
    (
      SELECT P.MATR,
             SUM(COALESCE(P.MNT, 0)) AS MNT_PRT
        FROM PRET P
    GROUP BY P.MATR
    ) PRT FULL OUTER JOIN
    (
      SELECT D.MATR,
             SUM(COALESCE(D.MNT, 0)) AS MNT_DMD
        FROM DEMANDE D
    GROUP BY D.MATR
    ) DMD ON DMD.MATR = PRT.MATR
ORDER BY 1 ASC;
 
MATR	MNT_ENDET
1	4500
2	2500
3	5000
4	0
5	0
6	0
7	7500
8	17000

Votre seconde requête (corrigée) :

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
SELECT
    T1.MATR,
    T1.A + T2.B MNT_ENDET
FROM
    ( SELECT P.MATR,
             SUM(NVL(P.MNT, 0)) A
        FROM PRET P
    GROUP BY P.MATR ) T1,
    ( SELECT D.MATR,
             SUM(NVL(D.MNT, 0)) B
        FROM DEMANDE D
    GROUP BY D.MATR ) T2
WHERE T1.MATR = T2.MATR
ORDER BY 1 ASC;
 
MATR	MNT_ENDET
1	4500
4	0
7	7500
8	17000

Vous n'écrivez pas votre requette pour tout les matricules étant donné que vous travaillez à matricule constant (= 00000) dans votre premiere réponse. D'ou ma réaction sur le full outer.

Pour le sum de coalesce, c'est vrai. J'ai tout faux !

(Sauf dans le cas présent ou l'auteur du post initial nous dit que ça ramene 0 ou 1000).

Faut il les lignes en outer ? Vu la requete initiale : pas de jointure externe, c'est pas clair du tout. Pb d'interpretation et surtout de modèle.

Quand au "mal à utiliser les outils" c'est une réaction, peut être excessive de ma part, à bien du code qui passe ici ou là ou on fait du PL et des tas de circonvolutions avec des vues dynamiques et des fonctions analytique alors qu'une approche plus simple et plus éfficace existe et quelques lignes de SQL. Dans votre premiere réponse, vous faite un outer join pour joindre 2 vues dynamiques d'une ligne chacune. Ca m'a fait réagir.
J'aurais pas du !

Vous avez bien raison, les fonctions analytiques, d'un premier abord, ça destabilise, mais c'est super. C'est pas la peine de les utiliser pour faire un simple group by non plus.

**NounaM** · 19/06/2009, 11h27

Merci à vous deux pour vos réponses elles m'ont été trés utiles, à la fin j'ai utilisé la premiere solution cité par jmguiche:

Select T1.A + T2.B
From
(SELECT SUM(NVL(PRET.MONTANT,0) A
FROM PRET
WHERE PRET.MATR = '00000000'
AND PRET.RESTANT > 0) T1,
(SELECT SUM(NVL(DEMANDE.MONTANT,0)) B
FROM DEMANDE, DIVERS
WHERE DEMANDE.ID_DEMANDE = DIVERS.ID_DEMANDE
AND DIVERS.DECI = 'X'
AND DIVERS.MATR = '00000000' ) T2

pour sa simplicité surtout et elle marche. pour répondre a votre question d'hier oui je suis sure des données et résultats auxquels je m'attendais!

Demande d'aide pour une petite requete

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager