Utiliser SQL pour interroger des données à l’aide de l’API Web Dataverse

Vous pouvez utiliser le langage SQL (Structured Query Language) pour interroger des données à partir de Microsoft Dataverse à l’aide de l’API web. Passez des commandes SQL SELECT via l’option sql de requête, à l’aide du nom du jeu d’entités de la table que vous souhaitez interroger.

Pour utiliser une requête SQL comme suit :

SELECT name 
FROM account AS a 
WHERE a.name LIKE 'Fourth Coffee'

Définissez, comme valeur encodée dans l’URL du paramètre de requête sql, une ressource d’ensemble d’entités correspondant à la table de base de votre requête. Dans ce cas, le nom du jeu d’entités est accounts.

Requête

GET [Organization URI]/api/data/v9.2/accounts?sql=SELECT%20name%20%0D%0AFROM%20account%20AS%20a%20%0D%0AWHERE%20a.name%20LIKE%20'Fourth%20Coffee' HTTP/1.1
Authorization: Bearer [REDACTED]
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="*"
Accept: application/json

Réponse

La réponse est similaire à ce que vous obtenez avec la requête OData équivalente :

/accounts?$select=name&$filter=contains(name,'Fourth Coffee')

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="*"

{
   "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountid)",
   "@Microsoft.Dynamics.CRM.totalrecordcount": -1,
   "@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
   "@Microsoft.Dynamics.CRM.globalmetadataversion": "173309522",
   "value": [
      {
         "@odata.etag": "W/\"173325408\"",
         "accountid": "0bdd4472-981d-f111-8341-0022482aa957",
         "name": "Fourth Coffee"
      }
   ]
}

Sélectionner des colonnes

Répertoriez les noms de colonnes spécifiques dans la SELECT clause, séparés par des virgules. Utilisez des alias de table pour qualifier les références de colonne et utiliser des alias de colonne pour renommer des champs de sortie.

L’exemple suivant sélectionne trois colonnes de la table account à l’aide d’un alias de table :

SELECT a.name, a.telephone1, a.websiteurl
FROM account AS a

Utilisez des alias de colonne pour renommer les champs de sortie :

SELECT a.name AS account_name, a.telephone1 AS phone
FROM account AS a

Les enregistrements retournés ressemblent à cet exemple :

{
   "@odata.etag": "W/\"174033617\"",
   "accountid": "667ec6df-4a22-f111-8342-0022482aa3a2",
   "account_name@OData.Community.Display.V1.AttributeName": "name",
   "account_name": "Wide World Importers",
   "phone@OData.Community.Display.V1.AttributeName": "telephone1",
   "phone": "(555) 100-0006"
}

Joindre des tables

Utilisez INNER JOIN ou LEFT JOIN pour combiner des lignes issues de deux tables ou davantage. Effectuez une jointure sur une colonne associée, généralement entre une clé primaire et une clé étrangère.

L’exemple suivant retourne des comptes et leurs contacts associés à l’aide d’une jointure interne :

SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid

Utilisez cette option LEFT JOIN pour inclure des comptes qui n’ont aucun contact associé :

SELECT a.name, c.fullname
FROM account AS a
LEFT JOIN contact AS c ON a.accountid = c.parentcustomerid

Vous pouvez joindre plus de deux tables. L’exemple suivant joint des comptes, des contacts et des opportunités :

SELECT a.name, c.fullname, o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
INNER JOIN opportunity AS o ON a.accountid = o.customerid

Utilisez une jointure automatique pour lier des lignes dans la même table. L’exemple suivant recherche les comptes et leurs comptes parents :

SELECT child.name AS account, parent.name AS parent_account
FROM account AS child
INNER JOIN account AS parent ON child.parentaccountid = parent.accountid

Filtres ON supplémentaires

JOIN ... ON La clause doit utiliser l’opérateur = entre les colonnes issues des deux tables. Tous les filtres supplémentaires doivent être combinés avec cette condition d’égalité à l’aide de l’opérateur AND et doivent être appliqués à la table jointe.

-- Not supported, must join on columns from the two tables
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON c.emailaddress1 LIKE 'B%'

-- Not supported, must use "="
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid <> c.parentcustomerid

-- Not supported, must combine additional filters using AND
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid = c.parentcustomerid OR c.emailaddress1 LIKE 'B%'

-- Not supported, additional filters must be on the joined table
-- SELECT a.name, c.fullname, c.emailaddress1
-- FROM account AS a
-- INNER JOIN contact AS c ON a.accountid = c.parentcustomerid AND a.name LIKE 'A%'

-- This example works because it has a filter on the joined contact table fullname column:
SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid AND c.fullname LIKE 'A%'

Vous pouvez combiner des conditions supplémentaires entre elles à l’aide d’un opérateur imbriqué OR, à condition que l’ensemble du filtre supplémentaire soit combiné à une égalité de colonne à l’aide de AND :

SELECT a.name, c.fullname, c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
   ON a.accountid = c.parentcustomerid
   AND (c.fullname LIKE 'A%' OR c.emailaddress1 LIKE 'B%')

Trier les lignes

Permet ORDER BY de trier les résultats d’une ou plusieurs colonnes. Spécifiez ASC (croissant, par défaut) ou DESC (décroissant).

L’exemple suivant retourne des comptes triés par nom :

SELECT name, telephone1
FROM account
ORDER BY name ASC

Trier par plusieurs colonnes :

SELECT name, createdon
FROM account
ORDER BY name ASC, createdon DESC

Filtrer les lignes

Utilisez une WHERE clause pour filtrer les lignes par une ou plusieurs conditions. La WHERE clause doit comparer une colonne à une valeur constante.

Opérateurs de comparaison

Les opérateurs de comparaison pris en charge sont les suivants : =, , !=<>, <>, , <=, et >=.

SELECT name, statecode
FROM account
WHERE statecode = 0

Utilisez != ou <> pour exclure des lignes.

SELECT name, statecode
FROM account
WHERE statecode <> 1

Utilisez <, >, <=ou >= pour les comparaisons de plages.

SELECT name
FROM account
WHERE name > 'M'
ORDER BY name

Opérateurs logiques

Combiner des conditions à l’aide AND et OR. Utilisez des parenthèses pour contrôler l’ordre d’évaluation.

SELECT name, telephone1
FROM account
WHERE statecode = 0 AND telephone1 IS NOT NULL

SELECT name
FROM account
WHERE (name = 'Contoso' OR name = 'Fabrikam')

SELECT name, telephone1
FROM account
WHERE (statecode = 0 OR statecode = 1) AND telephone1 IS NOT NULL

Modèles LIKE

Utilisez LIKE pour faire correspondre des motifs de chaîne. Les caractères génériques pris en charge sont les suivants :

SELECT name FROM account WHERE name LIKE 'Fourth%'

Permet NOT LIKE d’exclure les lignes correspondantes :

SELECT name FROM account WHERE name NOT LIKE '%test%'

IN et NOT IN

Permet IN de faire correspondre n’importe quelle valeur dans une liste :

SELECT name
FROM account
WHERE name IN ('Contoso', 'Fabrikam', 'Fourth Coffee')

Permet NOT IN d’exclure des valeurs :

SELECT name
FROM account
WHERE name NOT IN ('Contoso', 'Fabrikam')

BETWEEN

Utilisez BETWEEN pour filtrer les lignes dans une plage incluse.

SELECT name
FROM account
WHERE name BETWEEN 'A' AND 'B'

IS NULL et IS NOT NULL

Permet IS NULL de rechercher des lignes où une colonne n’a aucune valeur et d’utiliser IS NOT NULL pour rechercher des lignes où une colonne a une valeur.

SELECT name
FROM account
WHERE telephone1 IS NULL

SELECT name, telephone1
FROM account
WHERE telephone1 IS NOT NULL

DISTINCT

Permet DISTINCT de retourner des valeurs uniques.

SELECT DISTINCT a.address1_city
FROM account AS a

Utilisation des fonctions DATEADD et GETUTCDATE

Utilisez la DATEADD fonction pour retourner des lignes pour une plage de dates constante :

-- Do not pass column values to functions
-- SELECT a.name
-- FROM account a
-- WHERE DATEADD(day, 3, a.createdon) >= '2023-01-01 17:00:00' (not supported)

SELECT a.name
FROM account a
WHERE a.createdon >= DATEADD(day, -3, '2023-01-01 17:00:00')

Utilisez la fonction GETUTCDATE pour définir la plage par rapport à l’heure courante :

-- Do not pass column values to functions
-- SELECT a.name
-- FROM account a
-- WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE() (not supported)

SELECT a.name
FROM account a
WHERE a.createdon >= DATEADD(day, -3, GETUTCDATE())

Fonctionnalités de la clause WHERE non prises en charge

La WHERE clause ne prend pas en charge les fonctionnalités suivantes :

• Sous-requêtes : WHERE accountid IN (SELECT accountid FROM account).
• EXISTS et NOT EXISTS: Ces opérateurs retournent une erreur.
• Comparaisons littérales à littérales : WHERE 1=1 et WHERE 1=0.
• Comparaisons de colonnes à colonnes : WHERE a.modifiedon > a.createdon.
• Expressions : WHERE a.revenue > 500.0 + 125.0.
• Fonctions appliquées aux valeurs de colonne : WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE().
• Fonctions non répertoriées dans ce document.

Résultats de la page

Utilisez la pagination OData avec l’en-tête Prefer: odata.maxpagesize de requête et l’annotation @odata.nextLink . En savoir plus sur la pagination.

Vous pouvez également utiliser une approche basée sur le curseur en filtrant sur le dernier ID vu de la page précédente :

SELECT name, accountid
FROM account
WHERE accountid > '00000000-0000-0000-0000-000000000000'
ORDER BY accountid

Données agrégées

Utilisez des fonctions d’agrégation avec GROUP BY pour synthétiser les données. Les fonctions d’agrégation prises en charge sont COUNT, , SUMAVG, MINet MAX.

L’exemple suivant regroupe les contacts par leur compte parent et les compte :

SELECT a.name, COUNT(*) AS contact_count
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
GROUP BY a.name
ORDER BY a.name

Utilisez plusieurs fonctions d’agrégation dans une seule requête :

SELECT COUNT(*) AS total_accounts,
       SUM(revenue) AS total_revenue,
       AVG(revenue) AS avg_revenue,
       MIN(revenue) AS min_revenue,
       MAX(revenue) AS max_revenue
FROM account

Limites d’enregistrements des requêtes d’agrégation

Les requêtes qui renvoient des valeurs agrégées sont limitées à 50 000 enregistrements. Cette limite permet de maintenir les performances et la fiabilité du système. Si les critères de filtre de votre requête renvoient plus de 50 000 enregistrements, vous obtenez le message d’erreur suivant :

Nombre : -2147164125
Code : 8004E023
Message : AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Message d’erreur client : La limite d’enregistrement maximale est dépassée. Réduisez le nombre d’enregistrements.

Pour éviter cette erreur, ajoutez des filtres appropriés à votre requête pour vous assurer qu’elle n’évalue pas plus de 50 000 enregistrements. Exécutez ensuite votre requête plusieurs fois et combinez les résultats. Les filtres appropriés dépendent de la nature de vos données, mais il peut s’agir d’une plage de dates ou d’un sous-ensemble de valeurs dans une colonne de choix.

Compter les lignes

Permet COUNT(*) de compter le nombre de lignes qui correspondent à votre requête :

SELECT COUNT(*) AS account_count
FROM account

Combinez COUNT avec une WHERE clause pour compter les lignes qui répondent à une condition :

SELECT COUNT(*) AS active_contacts
FROM contact
WHERE statecode = 0

Utilisez COUNT avec GROUP BY pour compter les lignes par groupe :

SELECT a.name, COUNT(*) AS contact_count
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
GROUP BY a.name

Optimiser les performances

Suivez ces instructions pour écrire des requêtes SQL efficaces sur Dataverse.

Évitez les anti-modèles de requête

Pour obtenir des conseils sur les aspects généraux à éviter lors de la composition de requêtes Dataverse, consultez Anti-modèles de requête.

Sélectionnez uniquement les colonnes dont vous avez besoin

La sélection de moins de colonnes réduit la quantité de données transférées. Évitez de demander des colonnes que vous n’utilisez pas :

-- Avoid selecting all columns
-- SELECT * FROM account (not supported)

-- Select only needed columns
SELECT name, telephone1
FROM account

Filtrer sur les colonnes indexées

Le filtrage sur les clés primaires et d’autres colonnes indexées est plus rapide que le filtrage sur les champs non indexés.

SELECT name, telephone1
FROM account
WHERE accountid = '00000000-0000-0000-0000-000000000000'

Limiter la profondeur de JOIN

Vous pouvez utiliser des jointures multitables, mais chaque jointure supplémentaire augmente le coût de la requête. Limitez les jointures à ce dont vous avez besoin pour votre requête.