Usar o SQL para consultar dados usando a API Web do Dataverse

Você pode usar SQL (Structured Query Language) para consultar dados de Microsoft Dataverse usando a API Web. Passe comandos SQL SELECT por meio da opção sql de consulta, usando o nome do conjunto de entidades da tabela que você deseja consultar.

Note

Cada comando deve conter uma única SELECT instrução. Outras instruções T-SQL, como DECLARE, INSERTDELETEou ALTER TABLE não têm suporte. Comandos com vários conjuntos de resultados como SELECT name FROM account; SELECT fullname FROM contact não têm suporte.

Para usar uma consulta SQL como esta:

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

Defina o valor codificado em URL da consulta para a opção sql de consulta para um recurso de conjunto de entidades que corresponda à tabela base da consulta. Nesse caso, o nome do conjunto de entidades é accounts.

Solicitação

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

Resposta

A resposta é semelhante ao que você obtém com a consulta 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"
      }
   ]
}

Selecionar colunas

Listar nomes de coluna específicos na SELECT cláusula, separados por vírgulas. Use aliases de tabela para qualificar as referências de coluna e use aliases de coluna para renomear os campos de saída.

Importante

Não há suporte para SELECT *. Você deve nomear explicitamente cada coluna que deseja recuperar.

O exemplo a seguir seleciona três colunas da account tabela usando um alias de tabela:

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

Use aliases de coluna para renomear os campos de saída:

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

Os registros retornados são semelhantes a este exemplo:

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

Não há suporte para a seleção de valores literais, expressões e funções diferentes de agregações. Não use SELECT 'abc', 1+2 AS IntValue, DATEADD(day, -3, a.modifiedon), a.name FROM account a.

Unir tabelas

Use INNER JOIN ou LEFT JOIN para combinar linhas de duas ou mais tabelas. Faça uma junção em uma coluna relacionada, normalmente de uma chave primária para uma chave estrangeira.

Note

Não há suporte para RIGHT JOIN, FULL OUTER JOIN e CROSS JOIN.

O exemplo a seguir retorna contas e seus contatos relacionados usando uma junção 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 contas que não têm contatos relacionados:

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

Você pode unir mais de duas tabelas. O exemplo a seguir une contas, contatos e 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 uma autojunção para relacionar linhas dentro da mesma tabela. O exemplo a seguir localiza contas e suas respectivas contas principais:

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 adicionais

A cláusula JOIN ... ON deve usar um operador = entre colunas das duas tabelas. Todos os filtros adicionais devem ser combinados com essa condição de igualdade usando o AND operador e devem ser aplicados à tabela unida.

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

Você pode combinar condições adicionais entre si usando um operador aninhado OR , desde que todo o filtro adicional seja combinado com a igualdade de colunas usando 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 linhas

Use ORDER BY para classificar os resultados por uma ou mais colunas. Especifique ASC (crescente, o valor padrão) ou DESC (decrescente).

Note

ORDER BY só pode referenciar nomes de coluna. Expressões como ORDER BY LEN(name) não têm suporte.

O exemplo a seguir retorna contas classificadas por nome:

SELECT name, telephone1
FROM account
ORDER BY name ASC

Classificar por várias colunas:

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

Filtrar linhas

Use uma WHERE cláusula para filtrar linhas por uma ou mais condições. A WHERE cláusula deve comparar uma coluna com um valor constante.

Importante

Não há suporte para expressões e subconsultas nas cláusulas WHERE. A comparação deve estar entre uma coluna e um valor literal ou uma função com suporte.

Operadores de comparação

Os operadores de comparação com suporte são: =, , !=, <>, <, , >, e <=>=.

SELECT name, statecode
FROM account
WHERE statecode = 0

Use != ou <> para excluir linhas.

SELECT name, statecode
FROM account
WHERE statecode <> 1

Use<, >ou <=>= para comparações de intervalo.

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

Operadores lógicos

Combinar condições usando AND e OR. Use parênteses para controlar a ordem de avaliação.

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

Padrões LIKE

Use LIKE para corresponder aos padrões de cadeia de caracteres. Os curingas suportados são:

Curinga	Description	Exemplo
%	Corresponde a qualquer sequência de caracteres	'Fourth%' corresponde a Fourth Coffee
_	Corresponde a qualquer caractere único	'_ontoso' corresponde a Contoso
[%]	Corresponde a um símbolo de porcentagem literal	'[%]off' corresponde a 50%off

SELECT name FROM account WHERE name LIKE 'Fourth%'

Use NOT LIKE para excluir linhas correspondentes:

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

Dica

Evite caracteres curinga à esquerda (LIKE '%value') quando possível: eles exigem uma verificação completa da tabela e prejudicam o desempenho. Um curinga à direita (LIKE 'value%') pode usar um índice. Para saber mais, consulte Evite caracteres curinga iniciais nas condições de filtro.

IN e NOT IN

Use IN para corresponder a qualquer valor em uma 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

Use BETWEEN para filtrar linhas dentro de um intervalo inclusivo.

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

IS NULL and IS NOT NULL

Use IS NULL para localizar linhas em que uma coluna não tem valor e use IS NOT NULL para localizar linhas em que uma coluna tenha um valor.

SELECT name
FROM account
WHERE telephone1 IS NULL

SELECT name, telephone1
FROM account
WHERE telephone1 IS NOT NULL

Note

Não use = NULL para testar valores nulos. Use IS NULL em seu lugar. A expressão WHERE name = NULL não retorna os resultados esperados.

DISTINCT

Use DISTINCT para retornar valores exclusivos.

SELECT DISTINCT a.address1_city
FROM account AS a

Usando funções DATEADD e GETUTCDATE

Note

Você deve aplicar funções a um valor literal ou a outra função compatível. Você não pode aplicar funções a valores de coluna.

Use a DATEADD função para retornar linhas para um intervalo de datas 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 a GETUTCDATE função para tornar o intervalo relativo à hora atual:

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

As condições de cláusula WHERE e ON dão suporte a estas funções. As SELECTcláusulas , ORDER BYe GROUP BY não dão suporte a chamadas de função.

Recursos da cláusula WHERE sem suporte

A WHERE cláusula não dá suporte aos seguintes recursos:

• Subconsultas: WHERE accountid IN (SELECT accountid FROM account).
• EXISTS e NOT EXISTS: esses operadores retornam um erro.
• Comparações literais a literais: WHERE 1=1 e WHERE 1=0.
• Comparações de coluna a coluna: WHERE a.modifiedon > a.createdon.
• Expressões: WHERE a.revenue > 500.0 + 125.0.
• Funções aplicadas a valores de coluna: WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE().
• Funções não listadas neste documento.

Resultados da página

Use a paginação OData com o cabeçalho de solicitação Prefer: odata.maxpagesize e a anotação @odata.nextLink. Saiba mais sobre paginação.

Note

TOP e OFFSET ... FETCH não têm suporte em consultas. Use Prefer: odata.maxpagesize para limitar o número de registros.

Como alternativa, use uma abordagem baseada em cursor filtrando a última ID vista da página anterior:

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

Agregar dados

Use funções de agregação com GROUP BY para resumir dados. As funções de agregação com suporte são COUNT, SUM, AVG, MIN e MAX.

Note

Não há suporte para HAVING. Filtre dados usando uma WHERE cláusula antes de agregar.

O exemplo a seguir agrupa os contatos por sua conta principal e os contabiliza:

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 várias funções de agregação em uma única 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

Não há suporte para agrupamento por funções, incluindo por partes da data, como GROUP BY MONTH(a.createdon).

Limites de registros de consultas agregadas

As consultas que retornam valores agregados são limitadas a 50.000 registros. Esse limite ajuda a manter o desempenho e a confiabilidade do sistema. Se os critérios de filtro na consulta retornarem mais de 50.000 registros, você receberá o seguinte erro:

Número: -2147164125
Código: 8004E023
Mensagem: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Mensagem de erro do cliente: o limite máximo de registro é excedido. Reduzir o número de registros.

Para evitar esse erro, adicione filtros apropriados à consulta para garantir que ela não avalie mais de 50.000 registros. Em seguida, execute a consulta várias vezes e combine os resultados. Os filtros apropriados dependem da natureza dos dados, mas podem ser um intervalo de datas ou um subconjunto de valores em uma coluna de escolha.

Contar linhas

Use COUNT(*) para contar o número de linhas que correspondem à consulta:

SELECT COUNT(*) AS account_count
FROM account

Combine COUNT com uma WHERE cláusula para contar linhas que atendam a uma condição:

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

Use COUNT com GROUP BY para contar linhas 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

Otimizar o desempenho

Siga estas diretrizes para escrever consultas SQL eficientes no Dataverse.

Evitar antipadrões de consulta

Para obter orientações sobre aspectos gerais a serem evitados ao elaborar consultas no Dataverse, consulte antipadrões de consulta.

Selecione apenas as colunas de que você precisa

Selecionar menos colunas reduz a quantidade de dados transferidos. Evite solicitar colunas que você não usa:

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

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

Filtrar em colunas indexadas

A filtragem em chaves primárias e outras colunas indexadas é mais rápida do que filtrar em campos não indexados.

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

Limitar a profundidade do JOIN

Você pode usar junções entre várias tabelas, mas cada junção adicional aumenta o custo da consulta. Limite as junções ao necessário para sua consulta.