L’opérateur d’intra jointure APPLY

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  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire