Discussion :

[Foxtrot]

Bonjour,

J'ai un problème pour composer une jointure sur des conditions variables avec Oracle.

J'ai une table de résultats chimiques (près de 2 millions de lignes, exemple simplifié) pour des métaux, hydrocarbures, composés organiques, pesticides, etc.

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE Resultat
(chemical_name NVARCHAR2(255),
 valeur NUMBER(30,15))
 
INSERT INTO Resultat (chemical_name, valeur) VALUES ('Plomb', 4)
INSERT INTO Resultat (chemical_name, valeur) VALUES ('Plomb dissous', 58)
INSERT INTO Resultat (chemical_name, valeur) VALUES ('Uranium', 0.5)
INSERT INTO Resultat (chemical_name, valeur) VALUES ('Zinc', 9)
INSERT INTO Resultat (chemical_name, valeur) VALUES ('Calcium dissous', 2541)

J'ai aussi une table de critères pour valider les résultats (environ 300 lignes)

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Critere
(SUBSTANCE NVARCHAR2(255),
 Scenario_1 NUMBER(30,15),
 Scenario_2 NUMBER(30,15))
 
INSERT INTO Critere (SUBSTANCE, Scenario_1, Scenario_2) VALUES ('Plomb', 5, 12)
INSERT INTO Critere (SUBSTANCE, Scenario_1, Scenario_2) VALUES ('Plomb dissous', 8, 10)
INSERT INTO Critere (SUBSTANCE, Scenario_1, Scenario_2) VALUES ('Zinc dissous', 15, 50)
INSERT INTO Critere (SUBSTANCE, Scenario_1, Scenario_2) VALUES ('Calcium dissous', 2000, 5000)
INSERT INTO Critere (SUBSTANCE, Scenario_1, Scenario_2) VALUES ('Nitrate', 10, 18)

Je veux un affichage où chaque résultat est accolé à ses critères (si applicable) pour comparaison ultérieure.
Tout ça fonctionne bien.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT chemical_name, valeur, substance, scenario_1, scenario_2 
FROM Resultat 
LEFT JOIN Critere 
ON chemical_name = substance
 
chemical_name	valeur	substance	scenario_1	scenario_2
_____________	______	_________	__________	__________
Plomb		4	Plomb		5		12
Plomb dissous	58	Plomb dissous	8		10
Uranium		0.5
Zinc		9
Calcium dissous	2541	Calcium dissous	2000		5000

J'ai besoin de modifier cette requête pour traiter des conditions particulières. Si un métal n'a pas de critères mais que des critères existent pour la version dissoute, je veux faire cette jointure (voir l'exemple du zinc).

Code :

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

1
2
3
4
5
6
7
chemical_name	valeur	substance	scenario_1	scenario_2
_____________	______	_________	__________	__________
Plomb		4	Plomb		5		12
Plomb dissous	58	Plomb dissous	8		10
Uranium		0.5
Zinc		9	Zinc dissous	15		50
Calcium dissous	2541	Calcium dissous	2000		5000

La requête qui s'en approche est :

Code :

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

1
2
3
4
SELECT chemical_name, valeur, substance, scenario_1, scenario_2 
FROM Resultat 
LEFT JOIN Critere 
ON ((chemical_name = substance) OR (chemical_name || ' dissous' = substance))

mais certains résultats sont dédoublés (voir le plomb 4):

Code :

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

1
2
3
4
5
6
7
8
chemical_name	valeur	substance	scenario_1	scenario_2
_____________	______	_________	__________	__________
Plomb		4	Plomb		5		12
Plomb 		4	Plomb dissous	8		10
Plomb dissous	58	Plomb dissous	8		10
Uranium		0.5
Zinc		9	Zinc dissous	15		50
Calcium dissous	2541	Calcium dissous	2000		5000

Comment composer cette jointure sans dédoubler les résultats de plusieurs métaux ?

Merci.
Christian

[ectap]

Bonjour,

Il faut résonner comme si tu le faisais "à la main":
Pour chaque ligne de la table Resultat

• Je cherche dans la table critere une ligne avec substance = chemical_name

• Si je n'ai pas trouvé de ligne, je cherche dans la table critère une ligne avec substance = chemical_name suffixé par " dissous"

Il faut donc faire 2 jointures gauches avec la table Critere, la 1ère sans ajouter "dissous" à chemical_name, la 2ème en ajoutant dissous
Si la 1ère remonte des données tu les récupère sinon tu prend les données de la 2ème:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT chemical_name, valeur
	, CASE WHEN C1.substance IS NOT NULL THEN C1.substance ELSE C2.substance END AS substance
	, CASE WHEN C1.substance IS NOT NULL THEN C1.scenario_1 ELSE C2.scenario_1 END AS scenario_1
	, CASE WHEN C1.substance IS NOT NULL THEN C1.scenario_2 ELSE C2.scenario_2 END AS scenario_2
FROM Resultat 
LEFT JOIN Critere AS C1
	ON chemical_name = C1.substance
LEFT JOIN Critere AS C2
	ON chemical_name || ' dissous' = C2.substance

[al1_24]

Il faut en effet passer par deux étapes. D'abord le scénario qui correspond à la substance, ensuite celui de la substance dissoute SI le premier n'est pas trouvé :

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
SELECT  chemical_name, valeur, substance, scenario_1, scenario_2 
FROM    Resultat 
    INNER JOIN
        Critere 
        ON  chemical_name = substance
UNION ALL
SELECT  chemical_name, valeur, substance, scenario_1, scenario_2 
FROM    Resultat    AS  res 
    LEFT JOIN
        Critere     AS crt 
        ON  res.chemical_name || ' dissous' = crt.substance
WHERE   NOT EXISTS
        (   SELECT  NULL 
            FROM    Critere AS  exc
            WHERE   res.chemical_name = exc.substance
        )

[Foxtrot]

La solution avec les CASE WHEN fonctionne à merveille, très lisible et facile à adapter pour un cas particulier que j'ai découvert en faisant les tests. J'avais exploré l'idée des CASE mais sans penser à joindre deux fois la même table. La seconde solution fonctionne aussi mais l'adaptation au cas particulier est plus compliquée.

Dans les deux cas, il faut enlever les AS entre les noms des tables et les alias.

Merci beaucoup à vous deux.