Jointures externes complètes pour Sources de données relationnelles

La commande jointure WebFOCUS, ainsi que la commande de jointure conditionnelle, offre toutes les deux l'option de jointure FULL OUTER (join).

Une jointure externe renvoie toutes les lignes de la sources de données source et toutes les lignes de la sources de données cible. Lorsque aucune valeur n'existe pour les lignes dans l'une des sources de données, des valeurs null sont renvoyées. WebFOCUS substitut les valeurs par défaut sur la sortie du rapport (vide pour colonnes alphanumériques, symbole NODATA pour colonnes numériques).

La jointure sterne complète ne supportait que pour l'utilisation avec des sources de données relationnelles supportant elles-mêmes ce type de jointure, et dans un tel cas, la syntaxe de jointure WebFOCUS est optimisée (traduites en syntaxe SQL de jointure externe complète supportée par le SGBDR). Un message d'erreur sera généré dans le cas de l'utilisation de cette syntaxe pour toute source de données ne supportant pas une jointure externe complète ou dans le cas d'un échec de l'optimisation de la requête par le moteur.

Syntaxe : Spécifier une jointure externe complète

La syntaxe suivante génère une jointure externe complète sur des champs réels :

JOIN FULL_OUTER hfld1 [AND hfld2 ...] IN table1 [TAG tag1] TO {UNIQUE|MULTIPLE} cfld [AND cfld2 ...] IN table2 [TAG tag2] [AS joinname]
END

où :

hfld1

Est le nom d'un champ dans la table hôte contenant des valeurs partagées par un champ dans la table à référence croisée. Ce champ est appelé le "champ hôte".

AND hfld2...

Peut être un champ supplémentaire dans la table hôte. La phrase commençant par AND est requise lors pour la spécification de champs multiples.

Vous pouvez spécifier jusqu'à 16 champs pour les adaptateurs relationnels qui supportent les jointures concaténées et multichamp. Consultez la documentation relative à votre adaptateur pour des informations spécifiques sur les fonctions de jointure que celui-ci prend en charge.

IN table1

Est le nom de la table hôte.

TAG tag1

Est un nom de balise comptant jusqu'à 66 caractères (généralement le nom du fichier Master), utilisé comme qualificateur unique pour les champs et alias de la table hôte.

Le nom de balise de la table hôte doit être le même dans toutes les commandes JOIN d'une structure jointe.

TO [UNIQUE|MULTIPLE] crfld1

Est le nom d'un champ dans la table à référence croisée contenant des valeurs qui correspondent à celles de hfld1 (ou à celles des champs hôtes concaténés). Ce champ est appelé le "champ de référence".

Remarque : UNIQUE ne retourne qu'une seule instance et, dans le cas où il n'existe aucune instance correspondante dans la table à référence croisée, renvoie les valeurs null.

Utilisez le paramètre MULTIPLE lorsque crfld1 risque d'avoir plusieurs instances en commun avec une valeur du champ hfld1. Il est important de noter que ALL est synonyme de MULTIPLE et que l'absence totale de ce paramètre est synonyme d'UNIQUE.

AND crfld2...

Est le nom d'un champ dans la table à référence croisée ayant des valeurs communes avec hfld2.

Remarque : crfld2 peut être qualifié. Ce champ n'est disponible que pour les adaptateurs prenant en charge les jointures à champs multiples.

IN crfile

Est le nom de la table à référence croisée.

TAG tag2

Est un nom de balise comptant jusqu'à 8 caractères (généralement le nom du fichier Master), utilisé comme qualificateur unique pour les champs et les alias dans les tables à références croisées. Dans une structure de jointure récursive, si aucun nom de balise n'est fourni, tous les noms de champ et tous les alias ont pour préfixe les quatre premiers caractères du nom de la jointure.

Le nom de balise de la table hôte doit être le même dans toutes les commandes JOIN d'une structure jointe.

AS joinname

Est un nom facultatif de 8 caractères maximum, que vous pouvez attribuer à la structure de jointure. Vous devez attribuer un nom unique à la structure de jointure si :

Vous voulez vous assurer qu'elle ne sera pas remplacée plus tard par une commande JOIN.
Vous voulez l'effacer sélectivement plus tard.
La structure est récursive.

Remarque : si vous n'attribuez pas de nom à la structure de jointure avec la phrase AS, il est supposé que le nom est blanc. Une jointure sans nom remplace une jointure existante sans nom.

FIN

Requis quand la commande JOIN à une longueur supérieure à ligne. Termine la commande et doit être placée sur une ligne.

La syntaxe suivante génère une jointure externe basée sur DEFINE :

JOIN FULL_OUTER deffld WITH host_field ... 
     IN table1 [TAG tag1]
     TO [UNIQUE|MULTIPLE] 
     cr_field IN table2 [TAG tag2] [AS joinname]
END

où :

deffld

Est le nom du champ virtuel du fichier hôte (le champ hôte). Le champ virtuel peut être défini dans le fichier maître ou par commande DEFINE.

WITH host_field

Est le nom de n'importe quel champ du segment hôte auquel vous souhaitez associer le champ virtuel. Cette association est requise pour localiser le champ virtuel.

La phrase WITH est requise, à moins que le paramètre KEEPDEFINES soit activé (ON) et que deffld ait été définie avant le lancement de la commande JOIN.

Pour savoir quel segment contient le champ virtuel, utilisez la commande de requête ? DEFINE après avoir lancé la commande DEFINE.

IN table1

Est le nom de la table hôte.

TAG tag1

Est un nom de balise de 66 caractères maximum (généralement le nom du fichier Master), utilisé comme qualificateur unique pour les champs et les alias des tables hôtes.

Le nom de balise du fichier hôte doit être le même dans toute commande JOIN d'une structure jointe.

TO [UNIQUE|MULTIPLE] crfld1

Est le nom d'un champ réel dans la table à référence croisée dont les valeurs correspondent à celles du champ virtuel. Il doit s'agir d'un champ réel, déclaré dans le fichier maître.

Remarque : UNIQUE ne retourne qu'une seule instance et, dans le cas où il n'existe aucune instance correspondante dans la table à référence croisée, renvoie les valeurs null.

IN crfile

Est le nom de la table à référence croisée.

TAG tag2

Est un nom de balise comptant jusqu'à 8 caractères (généralement le nom du fichier Master), utilisé comme qualificateur unique pour les champs et les alias dans les tables à références croisées. Dans une structure de jointure récursive, si aucun nom de balise n'est fourni, tous les noms de champs et tous les alias ont pour préfixe les quatre premiers caractères du nom de la jointure.

Le nom de balise du fichier hôte doit être le même dans toutes les commandes JOIN d'une structure jointe.

AS joinname

Est un nom facultatif de 8 caractères maximum, que vous pouvez attribuer à la structure de jointure. Vous devez attribuer un nom unique à la structure de jointure si :

Vous voulez vous assurer qu'elle ne sera pas remplacée plus tard par une commande JOIN.
Vous voulez l'effacer sélectivement plus tard.
La structure est récursive et que vous ne spécifiez pas de noms de balise.

Si vous n'attribuez pas de nom à la structure de jointure avec la phrase AS, le champ de nom sera considéré comme étant vide. Une jointure sans nom remplace une jointure existante sans nom.

FIN

Requis quand la commande JOIN à une longueur supérieure à ligne. Termine la commande et doit être placée sur une ligne.

La syntaxe suivante génère une jointure externe complète conditionnelle :

JOIN FULL_OUTER FILE table1 AT hfld1 [WITH hfld2] [TAG tag1]
     TO {UNIQUE|MULTIPLE} 
     FILE table2 AT crfld [TAG tag2] [AS joinname]
     [WHERE expression1;
     [WHERE expression2;
     ...]
END

où :

table1

Est le fichier Master hôte.

AT

Relie le segment parent, ou hôte, et le segment enfant, ou de référence, corrects. Les valeurs de champ utilisées comme paramètre AT ne sont pas utilisées pour créer le lien. Elles sont utilisées comme références de segment.

hfld1

Est le nom de champ du fichier Master dont le segment va être joint à la table à référence croisée. Le nom de champ doit se situer dans le segment au plus bas niveau de la source de données référencée.

tag1

Est le nom de balise facultatif utilisé comme qualificateur unique pour les champs et les alias dans la table hôte.

hfld2

Est le nom de la colonne de la table à laquelle une jointure conditionnelle basée sur DEFINE doit être associée. Pour une jointure conditionnelle basée sur DEFINE, le paramètre KEEPDEFINES doit avoir la valeur ON et les champs virtuels doivent être créés avant le lancement de la commande JOIN.

MULTIPLE

Spécifient une relation one-to-many entre table1 et table2. Veuillez noter qu'ALL est synonyme de MULTIPLE.

UNIQUE

Spécifient une relation one-to-one entre table1 et table2. Veuillez noter qu'ONE est un synonyme d'UNIQUE.

Remarque : la jointure sur UNIQUE ne va renvoyer qu'une seule instance de la table à référence croisée, et si cette instance n'a pas de correspondance basée sur l'évaluation de l'expression WHERE, des valeurs null sont renvoyées.

crfile

Est le fichier Master de référence.

crfld

Est le nom du champ de jointure dans le fichier Master de référence. Il peut être n'importe quel champ du segment.

tag2

Est le nom de balise facultatif utilisé comme qualificateur unique pour les champs et les alias de la table à référence croisée.

joinname

Est le nom associé à la structure jointe.

expression1, expression2

sont n'importe quelles expressions acceptables pour une commande DEFINE. Tous les champs utilisés dans ces expressions doivent se trouver dans un seul et même chemin.

FIN

La commande END est nécessaire pour quitter la commande et doit s'agir d'une ligne seule.

Haut de page

Exemple : Optimisation d'une jointure externe complète de tables Microsoft SQL Server

La requête suivante génère deux tables Microsoft SQL Server à joindre, puis effectue la requête sur la jointure. Les tables sont générées en utilisant l'exemple wf_retail que vous pouvez créer en utilisant le tutoriel WebFOCUS - Retail Demo dans la console Web du serveur.

La requête suivante génère la table WF_SALES. Le champ ID_PRODUCT sera utilisé dans la commande de jointure externe (outer join) complète. La table générée contiendra les valeurs de ID_PRODUCT allant de 2150 à 4000 :

TABLE FILE WF_RETAIL_LITE
SUM GROSS_PROFIT_US PRODUCT_CATEGORY PRODUCT_SUBCATEG
BY ID_PRODUCT
WHERE ID_PRODUCT FROM 2150 TO 4000
ON TABLE HOLD AS WF_SALES FORMAT SQLMSS
END

La requête suivante génère la table WF_PRODUCT. Le champ ID_PRODUCT sera utilisé dans la commande de jointure externe (outer join) complète. La table générée contiendra les valeurs de ID_PRODUCT allant de 3000 à 5000 :

TABLE FILE WF_RETAIL_LITE
SUM PRICE_DOLLARS PRODUCT_CATEGORY PRODUCT_SUBCATEG PRODUCT_NAME
BY ID_PRODUCT
WHERE ID_PRODUCT FROM 3000 TO 5000
ON TABLE HOLD AS WF_PRODUCT FORMAT SQLMSS
END

La requête suivante lance la commande JOIN et affiche les valeurs des tables jointes :

SET TRACEUSER=ON
SET TRACESTAMP=OFF
SET TRACEOFF=ALL
SET TRACEON = STMTRACE//CLIENT
JOIN FULL_OUTER ID_PRODUCT IN WF_PRODUCT TAG T1 TO ALL ID_PRODUCT IN WF_SALES TAG T2
 
TABLE FILE WF_PRODUCT
PRINT T1.ID_PRODUCT AS 'Product ID' 
PRICE_DOLLARS AS Price
T2.ID_PRODUCT AS 'Sales ID' 
GROSS_PROFIT_US
BY T1.ID_PRODUCT NOPRINT
ON TABLE SET PAGE NOPAGE
END

La trace montre que la jointure externe complète a été optimisée (traduit en SQL) pour que le serveur SQL puisse traiter la jointure :

SELECT   
T1."ID_PRODUCT",  
T1."PRICE_DOLLARS",  
T2."ID_PRODUCT",  
T2."GROSS_PROFIT_US"  
FROM   
( WF_PRODUCT T1  
FULL OUTER JOIN WF_SALES T2  
ON T2."ID_PRODUCT" = T1."ID_PRODUCT" )  
ORDER BY   
T1."ID_PRODUCT";

La sortie a une ligne pour chaque valeur ID_PRODUCT présente dans l'une des tables. Les lignes avec valeurs ID_PRODUCT allant de 2150 à 2167 ne sont présentes dans la table WF_SALES, les colonnes de WF_PRODUCT affichent donc le symbole NODATA. Les lignes avec valeurs ID_PRODUCT au-delà de 4000 ne sont présentes dans la table WF_PRODUCT, les colonnes de WF_SALES affichent donc le symbole NODATA. Les lignes avec des valeurs ID_PRODUCT allant de 2000 à 4000 sont présentes dans les deux tables, toutes les colonnes ont donc des valeurs, comme le montre l'image suivante.