Uso de SQL para consultar datos mediante la API web de Dataverse

Puede usar el lenguaje de consulta estructurado (SQL) para consultar datos de Microsoft Dataverse mediante la API web. Pase los comandos SQL SELECT a través de la sql opción de consulta mediante el nombre del conjunto de entidades de la tabla que desea consultar.

Note

Cada comando debe contener una sola SELECT instrucción. No se admiten otras instrucciones T-SQL como DECLARE, INSERT, DELETEo ALTER TABLE . No se admiten comandos con varios conjuntos de resultados como SELECT name FROM account; SELECT fullname FROM contact .

Para usar una consulta SQL como esta:

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

Establezca en la opción de consulta sql el valor de la consulta codificado en URL como un recurso de conjunto de entidades que se corresponda con la tabla base de la consulta. En este caso, el nombre del conjunto de entidades es accounts.

Solicitud

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

Respuesta

La respuesta es similar a la que obtiene con la consulta de OData equivalente:

/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"
      }
   ]
}

Seleccionar columnas

Enumera los nombres de columna específicos de la SELECT cláusula, separados por comas. Use alias de tabla para calificar las referencias de columna y usar alias de columna para cambiar el nombre de los campos de salida.

Importante

SELECT * no es compatible. Debe asignar un nombre explícito a cada columna que quiera recuperar.

En el ejemplo siguiente se seleccionan tres columnas de la account tabla mediante un alias de tabla:

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

Use alias de columna para cambiar el nombre de los campos de salida:

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

Los registros devueltos tienen un aspecto similar al de este ejemplo:

{
   "@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"
}

Note

No se admite la selección de valores literales, expresiones y funciones que no sean agregados. No use SELECT 'abc', 1+2 AS IntValue, DATEADD(day, -3, a.modifiedon), a.name FROM account a.

Unir tablas

Use INNER JOIN o LEFT JOIN para combinar filas de dos o más tablas. Unir mediante una columna relacionada, normalmente una clave primaria con una clave foránea.

Note

No se admiten RIGHT JOIN, FULL OUTER JOIN, y CROSS JOIN.

El siguiente ejemplo devuelve cuentas y sus contactos asociados mediante una combinación interna:

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

Use LEFT JOIN para incluir cuentas que no tengan contactos relacionados:

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

Puede unir más de dos tablas. En el ejemplo siguiente se unen cuentas, contactos y oportunidades:

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

Use una autocombinación para relacionar filas dentro de la misma tabla. En el ejemplo siguiente se encuentran cuentas y sus cuentas principales:

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

Filtros ON adicionales

JOIN ... ON cláusula debe utilizar un = operador entre las columnas de las dos tablas. Los filtros adicionales deben combinarse con esta condición de igualdad mediante el AND operador y deben aplicarse a la tabla combinada.

-- 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%'

Puede combinar condiciones adicionales entre sí mediante un operador anidado OR siempre que todo el filtro adicional se combine con la igualdad de columnas mediante 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%')

Ordenar filas

Use ORDER BY para ordenar los resultados por una o varias columnas. Especifique ASC (ascendente, valor predeterminado) o DESC (descendente).

Note

ORDER BY solo puede hacer referencia a nombres de columna. No se admiten expresiones como ORDER BY LEN(name) .

En el ejemplo siguiente se devuelven cuentas ordenadas por nombre:

SELECT name, telephone1
FROM account
ORDER BY name ASC

Ordenar por varias columnas:

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

Filtrar filas

Use una WHERE cláusula para filtrar las filas por una o varias condiciones. La WHERE cláusula debe comparar una columna con un valor constante.

Importante

Las expresiones y subconsultas no se admiten en las cláusulas WHERE. La comparación debe estar entre una columna y un valor literal o una función admitida.

Operadores de comparación

Los operadores de comparación admitidos son: =, !=, <><, , >, <=y >=.

SELECT name, statecode
FROM account
WHERE statecode = 0

Use != o <> para excluir filas.

SELECT name, statecode
FROM account
WHERE statecode <> 1

Use <, >, <=o >= para comparaciones de intervalos.

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

Operadores logicos

Combine condiciones mediante AND y OR. Use paréntesis para controlar el orden de evaluación.

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

Patrones de LIKE

Use LIKE para buscar coincidencias con patrones de cadena. Los caracteres comodín admitidos son:

Carácter comodín	Descripción	Example
%	Coincide con cualquier secuencia de caracteres	'Fourth%' coincide con Fourth Coffee
_	Coincide con cualquier carácter individual	'_ontoso' coincide con Contoso
[%]	Coincide con un signo de porcentaje literal	'[%]off' coincide con 50%off

SELECT name FROM account WHERE name LIKE 'Fourth%'

Use NOT LIKE para excluir las filas coincidentes:

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

Tip

Evite los caracteres comodín iniciales () siempreLIKE '%value' que sea posible: requieren un examen de tabla completo y dañan el rendimiento. Un carácter comodín final (LIKE 'value%') puede usar un índice. Para más información, consulte Evitar los caracteres comodín iniciales en condiciones de filtro.

IN y NOT IN

Use IN para que coincida con cualquier valor de una lista:

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

Use NOT IN para excluir valores:

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

BETWEEN

Se usa BETWEEN para filtrar filas dentro de un intervalo inclusivo.

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

IS NULL y IS NOT NULL

Use IS NULL para buscar filas en las que una columna no tenga ningún valor y use IS NOT NULL para buscar filas donde una columna tiene un valor.

SELECT name
FROM account
WHERE telephone1 IS NULL

SELECT name, telephone1
FROM account
WHERE telephone1 IS NOT NULL

Note

No use = NULL para probar valores NULL. Utilice IS NULL en su lugar. La expresión WHERE name = NULL no devuelve los resultados esperados.

DISTINCT

Use DISTINCT para devolver valores únicos.

SELECT DISTINCT a.address1_city
FROM account AS a

Uso de funciones DATEADD y GETUTCDATE

Note

Debe aplicar funciones a un valor literal u otra función admitida. No se pueden aplicar funciones a valores de columna.

Use la DATEADD función para devolver filas para un intervalo de fechas 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')

Use la GETUTCDATE función para convertir el intervalo en relación con la hora actual:

-- 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())

Note

Las condiciones de cláusula WHERE y ON admiten estas funciones. Las SELECTcláusulas , ORDER BYy GROUP BY no admiten llamadas de función.

Características no admitidas de la cláusula WHERE

La WHERE cláusula no admite las siguientes características:

• Subconsultas: WHERE accountid IN (SELECT accountid FROM account).
• EXISTS y NOT EXISTS: estos operadores devuelven un error.
• Comparaciones literales a literales: WHERE 1=1 y WHERE 1=0.
• Comparaciones de columna a columna: WHERE a.modifiedon > a.createdon.
• Expresiones: WHERE a.revenue > 500.0 + 125.0.
• Funciones aplicadas a valores de columna: WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE().
• Funciones no enumeradas en este documento.

Resultados de página

Use la paginación de OData con el encabezado de solicitud Prefer: odata.maxpagesize y la anotación @odata.nextLink. Obtenga más información sobre la paginación.

Note

TOP y OFFSET ... FETCH no se admiten en las consultas. Use Prefer: odata.maxpagesize para limitar el número de registros.

Como alternativa, utilice un enfoque basado en cursores filtrando por el último ID visto de la página anterior:

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

Datos agregados

Use funciones de agregado con GROUP BY para resumir los datos. Las funciones de agregado admitidas son COUNT, SUM, AVG, MINy MAX.

Note

HAVING no es compatible. Filtre los datos mediante una WHERE cláusula antes de agregarlos.

En el ejemplo siguiente se agrupan contactos por su cuenta principal y se los cuenta:

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

Use varias funciones de agregado en una sola consulta:

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

Note

No se admite la agrupación por funciones, incluidas las partes de fecha como GROUP BY MONTH(a.createdon), .

Límites de registros de consulta agregados

Las consultas que devuelven valores agregados están limitadas a 50 000 registros. Este límite ayuda a mantener el rendimiento y la confiabilidad del sistema. Si los criterios de filtro de la consulta devuelven más de 50 000 registros, obtendrá el siguiente error:

Número: -2147164125
Código: 8004E023
Mensaje: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Mensaje de error del cliente: se supera el límite máximo de registros. Reduzca el número de registros.

Para evitar este error, agregue filtros adecuados a la consulta para asegurarse de que no evalúa más de 50 000 registros. A continuación, ejecute la consulta varias veces y combine los resultados. Los filtros adecuados dependen de la naturaleza de los datos, pero podrían ser un intervalo de fechas o un subconjunto de valores en una columna de elección.

Recuento de filas

Use COUNT(*) para contar el número de filas que coinciden con la consulta:

SELECT COUNT(*) AS account_count
FROM account

Combine COUNT con una WHERE cláusula para contar filas que cumplan una condición:

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

Use COUNT con GROUP BY para contar filas por grupo:

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

Optimización del rendimiento

Siga estas instrucciones para escribir consultas SQL eficaces en Dataverse.

Evitar antipatrones de consulta

Para obtener instrucciones sobre los aspectos generales que se deben evitar al redactar consultas de Dataverse, consulte Antipatrones de consulta.

Seleccione solo las columnas que necesita.

Al seleccionar menos columnas, se reduce la cantidad de datos transferidos. Evite solicitar columnas que no use:

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

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

Filtrar por columnas indexadas

Filtrar por claves primarias y otras columnas indexadas es más rápido que filtrar por campos no indexados.

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

Limitar la profundidad de JOIN

Puede usar combinaciones multitable, pero cada combinación adicional aumenta el costo de la consulta. Limite las combinaciones a lo que necesita para la consulta.