L’opérateur d’intra jointure APPLY permet de réaliser des jointures entre une ligne d’une table et le contenu d’une colonne de la même table lorsque cette colonne est susceptible de contenir un table d’une manière ou d’une autre.
Il existe en fait deux possibilités de jointures pour cet opérateur : par produit cartésien (CROSS APPLY) ou en jointure externe (OUTER APPLY).
La jointure externe ne doit être utilisée que lorsque l’on veut produire une ligne, même en l’absence de données dans la colonne table (NULL).
En effet il ne s’agit pas de faire une jointure entre une table et une autre, mais entre une table et un objet qui n’est pas une table, mais une table en devenir (flux XML tabularisé ou fonction table), c’est donc une jointure à l’intérieure des données de la table, une fois que ces données auront donné naissance à la table dépliée, par exemple après application d’une fonction table ou d’un parsing XML.
En effet on ne peut pas faire une jointure avec un objet qui n’est pas une table, lorsque la génération du résultat de cet objet dépend d’un paramètre d’une autre table, la syntaxe ne l’autorise même pas.
Bien qu’il soit possible d’utiliser APPLY avec n’importe quelle table, son utilisation dans ce contexte relève de l’absurdité ou du masochisme. C’est selon.
DÉMONSTRATIONS…
Exemple 1 – jointure entre table et fonction table
1.1 – la fonction renvoyant une table
Création d’une fonction table renvoyant les 7 jours de la semaine à partir d’une date donnée
CREATE FUNCTION dbo.F_SEMAINE (@JOUR DATETIME)
RETURNS @T TABLE
(SMN_JID SMALLINT,
SMN_JOUR VARCHAR(12),
SMN_DATE DATETIME)
AS
BEGIN
WHILE DATEPART(weekday, @JOUR) <> 1
SET @JOUR = DATEADD(day, -1, @JOUR);
DECLARE @I SMALLINT;
SET @I = 1;
WHILE @I <= 7
BEGIN
INSERT INTO @T
VALUES (@I, DATENAME(weekday, @JOUR), @JOUR);
SET @JOUR = DATEADD(day, 1, @JOUR);
SET @I = @I + 1;
END;
RETURN;
END
GO
1.2 Рune table de facture et ses donn̩es :
CREATE TABLE dbo.T_FACTURE_FCT
(FCT_ID INT NOT NULL PRIMARY KEY,
FCT_DATE DATETIME,
FCT_MONTANT DECIMAL(16,2))
INSERT INTO dbo.T_FACTURE_FCT
VALUES (1, '05/04/2010', 500);
INSERT INTO dbo.T_FACTURE_FCT
VALUES (2, '14/04/2010', 600);
INSERT INTO dbo.T_FACTURE_FCT
VALUES (3, '22/04/2010', 800);
1.3 – QUESTION : Il faut envoyer les factures le jeudi suivant leur émission…
La tentative suivante échoue :
SELECT *
FROM dbo.T_FACTURE_FCT AS F
INNER JOIN dbo.F_SEMAINE(F.FCT_DATE) AS S
ON F.FCT_DATE < S.SMN_DATE
AND S.SMN_JOUR = 'jeudi'
Msg 4104, Niveau 16, État 1, Ligne 3
L’identificateur en plusieurs parties « F.FCT_DATE » ne peut pas être lié.
Le message d’erreur est clair : il n’est pas possible d’exécuter la fonction table « après » que le paramètre date ait été passé, puisqu’il faut faire la jointure « avant » !
Le fait de mettre un OUTER JOIN ne change rien.
La syntaxe correcte est donc :
SELECT *
FROM dbo.T_FACTURE_FCT AS F
CROSS APPLY dbo.F_SEMAINE(F.FCT_DATE) AS S
WHERE F.FCT_DATE < S.SMN_DATE
AND S.SMN_JOUR = 'jeudi'
Notez que l’on a une facture qui n’apparait pas, car la date cette facture est postérieure à la date de paiement. Ce cas peut être résolu de la manière suivante :
SELECT F.*, CASE
WHEN F.FCT_DATE >= S1.SMN_DATE THEN S2.SMN_DATE
ELSE S1.SMN_DATE
END AS DATE_PAIEMENT
FROM dbo.T_FACTURE_FCT AS F
CROSS APPLY dbo.F_SEMAINE(F.FCT_DATE) AS S1
CROSS APPLY dbo.F_SEMAINE(DATEADD(week, 1, F.FCT_DATE)) AS S2
WHERE S1.SMN_JOUR = 'jeudi'
AND S2.SMN_JOUR = 'jeudi'
En fait on génère deux fois la table des dates de semaine, l’une pour la selaine courante, l’autre pour la suivante et l’on choisit comme date de paiement le premier jeudi suivant la date de la facture.
Exemple 2 – jointure entre table et XML « déplié »
2.1 – Création table avec données XML (liste d’entreprises avec leurs employés sous forme XML)
CREATE TABLE dbo.T_ENTREPRISE_EMPLOIS_EEP
(EEP_ID INTEGER PRIMARY KEY IDENTITY,
EEP_NAME VARCHAR(12),
EEP_XML_DATA XML)
2.2 – Insertion données du jeu d’essais : listes d’employés pour les entreprises IBM et Microsoft
INSERT INTO dbo.T_ENTREPRISE_EMPLOIS_EEP
VALUES ('IBM',
'<Personnes>
<Personne>
<Nom>WATSON</Nom>
<Prenom>John</Prenom>
</Personne>
<Personne>
<Nom>CODD</Nom>
<Prenom>Frank Edgar</Prenom>
</Personne>
</Personnes>');
INSERT INTO dbo.T_ENTREPRISE_EMPLOIS_EEP
VALUES ('Microsoft',
'<Personnes>
<Personne>
<Nom>BROUARD</Nom>
<Prenom>Frédéric</Prenom>
</Personne>
<Personne>
<Nom>DAVEM</Nom>
<Prenom>Mike</Prenom>
</Personne>
</Personnes>');
2.3 – QUESTION : Il faut extraire, de toutes les entreprises, la liste des employés dont le nom contient la lettre « O »…
SELECT EEP_ID,
NoeudXML.value( './Nom[1]', 'varchar(32)' ) as Nom,
NoeudXML.value( './Prenom[1]', 'varchar(25)' ) as Prenom
FROM dbo.T_ENTREPRISE_EMPLOIS_EEP
CROSS JOIN EEP_XML_DATA.nodes('/Personnes/Personne') as T(NoeudXML)
WHERE NoeudXML.value( './Nom[1]', 'varchar(32)' ) LIKE '%O%'
Msg 207, Niveau 16, État 1, Ligne 5
Nom de colonne non valide : ‘EEP_XML_DATA’.
Msg 9506, Niveau 16, État 1, Ligne 1
La méthode XMLDT ‘nodes’ ne peut être appelée que dans les colonnes de type XML.
Le résultat est logique et le message cohérent : vu que le dépliage du XML sous forme de table « virtuelle » n’est effectif qu’après la jointure, les nÅ“uds (nom, prenom) ne peuvent donc pas être préalablement évalués.
Pour ce faire, il faut à nouveau recourir au CROSS APPLY :
SELECT EEP_ID,
NoeudXML.value( './Nom[1]', 'varchar(32)' ) as Nom,
NoeudXML.value( './Prenom[1]', 'varchar(25)' ) as Prenom
FROM dbo.T_ENTREPRISE_EMPLOIS_EEP
CROSS APPLY EEP_XML_DATA.nodes('/Personnes/Personne') as T(NoeudXML)
WHERE NoeudXML.value( './Nom[1]', 'varchar(32)' ) LIKE '%O%'
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
