Referência: expressões SQL
Você pode usar expressões SQL para vários finalidades de integração de dados. O suporte para os recursos da linguagem SQL varia de acordo com o sistema no qual a instrução SQL é executada. Por exemplo:
- Em consultas em dados que foram importados para a área de preparação podem ser usados todos os recursos da linguagem SQL.
- Em consultas direcionadas a sistemas externos, como o NetSuite, podem ser usados filtros SQL e um conjunto reduzido de recursos.
Para informações sobre limitações específicas de tipos de fontes de dados e notas de uso, consulte as seguintes seções neste documento:
- Filtros SQL no NetSuite
- Filtros SQL em planilha
- Filtros SQL em JDBC
- Filtros SQL no Salesforce
- Filtros SQL no Intacct
- Filtros SQL no Microsoft Dynamics GP
Oferecemos suporte somente a um conjunto limitado de expressões SQL. Oferecemos suporte a outras operações de maneiras diferentes no Design de integrações. Exemplo: para criar cláusulas JOIN em SQL, você pode adicionar tabelas e colunas de junção em SQL arrastando
Tabela de junção
da pasta Tabela personalizada
em Componentes de dados
para a área de tabela de uma fonte de dados.Valores literais
Constantes incorporadas, como valores de data e hora fixos ou cadeia de caracteres/números inalteráveis, em uma expressão que usa valores literais.
Tipo de dados | Sintaxe | Descrição | Exemplo de uso |
|---|---|---|---|
Texto | '*****' | Cadeia de caracteres de texto entre aspas simples. Ao incluir uma aspa simples no texto, sempre use uma segunda aspa simples para sinalizar o término do contexto | 'Está calor lá fora' |
Número inteiro | # | Inserido exatamente como deve ser exibido | 999 |
Ponto flutuante | #.# | Deve sempre conter um ponto (.) dentro do valor de ponto flutuante, mesmo quando a parte fracionária é 0 | 7.7 |
Data e hora | TIMESTAMP '*****' | Palavra-chave TIMESTAMP seguida por uma representação de data com comprimento constante, entre aspas simples, no formato yyyy-mm-dd hh:mm:ss.SSS
Você pode especificar três dígitos de precisão para segundos fracionários. O máximo é de 6 dígitos. | TIMESTAMP '1934-11-09 17:05:12.012345'
TIMESTAMP '2013-01-02 03:14:05.006006' TIMESTAMP '2013-01-02 03:14:05.006' TIMESTAMP '2013-01-02 03:14:05' |
Data | DATE '*****' | Palavra-chave DATE seguida por uma representação de data com comprimento constante, entre aspas simples, no formato yyyy-mm-dd | DATE '1879-03-14' |
Booliano | TRUE (ou FALSE) | Palavras reservadas TRUE ou FALSE | FALSE |
Operadores
Os operadores numéricos executam operações matemáticas em expressões, colunas ou valores numéricos (número inteiro, ponto flutuante, bit). Os operadores de texto combinam duas ou mais expressões de texto, valores ou colunas.
Aplica-se a | Operador | Descrição | Exemplo de uso |
|---|---|---|---|
Números | + | Soma dois valores numéricos | MyNumericColumn + 1000 |
Números | - | Subtrai o valor do lado direito do valor do lado esquerdo | MyNumericColumn - 1000 |
Números | / | Divide o valor do lado esquerdo pelo valor do lado direito | MyNumericColumn / 1000 |
Números | * | Multiplica dois valores | MyNumericColumn * 1000 |
Texto | || | Concatena dois valores de texto | MyTextColumn || 'um sufixo' |
Expressões de comparação e de lógica
Essas expressões são resolvidas como 1 (verdadeiro) ou 0 (falso) e podem ser usadas em expressões de junção de tabela ou como [expr] em comparações CASE WHEN [expr] THEN [valor] END. As expressões de comparação e de lógica podem operar em colunas de texto, numéricas ou de data e hora
Aplica-se a | Sintaxe | Descrição | Exemplo de uso |
|---|---|---|---|
Qualquer | = | Verifica se dois valores ou expressões são iguais | MyColumn1 = MyColumn2 |
Qualquer | <> | Verifica se dois valores ou expressões são diferentes | MyColumn1 <> MyColumn2 |
Qualquer | IS NULL | Verifica se um valor ou expressão é NULL (nulo). NULL não é equivalente a uma cadeia de caracteres vazia | MyColumn1 IS NULL |
Qualquer | IS NOT NULL | Verifica se um valor ou expressão é diferente de NULL | MyColumn1 IS NOT NULL |
Qualquer | < | Verifica se o valor ou expressão à esquerda é menor do que o valor ou expressão à direita | MyColumn1 < MyColumn2 |
Qualquer | <= | Verifica se o valor ou expressão à esquerda é menor ou igual ao valor ou expressão à direita | MyColumn1 <= MyColumn2 |
Qualquer | > | Verifica se o valor ou expressão à esquerda é maior do que o valor ou expressão à direita | MyColumn1 > MyColumn2 |
Qualquer | >= | Verifica se o valor ou expressão à esquerda é maior ou igual ao valor ou expressão à direita | MyColumn1 >= MyColumn2 |
Qualquer | IN | Verifica se um valor ou expressão está contido em um conjunto | MyColumn1 IN (1, 2, 3) |
Qualquer | NOT IN | Verifica se um valor ou expressão não está contido em um conjunto | MyColumn1 NOT IN (1, 2, 3) |
Texto | LIKE | Verifica se um valor de texto ou expressão corresponde a um padrão. O caractere % funciona como um caractere curinga | MyColumn1 LIKE '%Apple' |
Texto | NOT LIKE | Verifica se um valor de texto ou expressão não corresponde a um padrão. O caractere % funciona como um caractere curinga | MyColumn1 NOT LIKE '%Apple' |
Comparações | AND | Avalia duas comparações e retorna verdadeiro somente se ambas as expressões forem verdadeiras | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Comparações | OR | Avalia duas comparações e retorna verdadeiro se qualquer uma das expressões for verdadeira | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Funções escalares
As funções escalares usam valores de entrada e retornam um único valor
Sintaxe | Descrição | Exemplo de uso |
|---|---|---|
Funções de bit
| ||
CAST(expr AS BIT) | Converte um valor de Texto/Ponto flutuante/Número inteiro em um valor de bit (0 ou 1) | CAST('1' AS BIT) => 1 |
Funções de número inteiro
| ||
CAST(expr AS INTEGER) | Converte um valor de Texto/Ponto flutuante/Bit em um valor de número inteiro | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Obtém o número de dias (DAY) de [datepart] de [datetime_expr1] até [datetime_expr2] | TIMESTAMPDIFF(DAY FROM TIMESTAMP '2013-02-01 00:00:00.000' TO TIMESTAMP '20130210 00:00:00.000') => 9 |
DATEDIFF([datepart] FROM [date_expr1] TO [date_expr2]) | Obtém o número de dias (DAY) de [datepart] de [date_expr1] até [date_expr2] | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Obtém o ano/mês/dia/hora/minuto/segundo (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) de [datepart] de [datetime_expr] | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Obtém o comprimento de [text_expr] | LENGTH('Hello') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Obtém o primeiro índice de [find_text_expr] em [search_text_expr]. O primeiro caractere é 1. | POSITION('at' IN 'hat') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Recupera o primeiro índice de [find_text_expr] em [search_text_expr] após o índice [start] (um [start] de -1 significa localizar o último). O primeiro caractere é 1. | POSITION('a' IN 'a hat' FROM 1) => 4 |
Funções de ponto flutuante
| ||
CAST(expr AS FLOAT) | Converte um valor de Texto/Número inteiro/Bit em um valor de ponto flutuante | CAST('1.01' AS FLOAT) => 1.01 |
Funções de texto
| ||
CAST(expr AS NVARCHAR) | Converte um valor de Ponto flutuante/Número inteiro/Bit em um valor de Texto | CAST(1.01 AS NVARCHAR) => '1.01' |
TRIM([text_expr]) | Remove os espaços à esquerda e à direita de [text_expr] | TRIM ('xxx') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extrai uma parte de [text_expr] iniciando na posição [start_int_expr]. O primeiro caractere está na posição 1 | SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Extrai [len_int_expr] caracteres de [text_expr] iniciando na posição [start_int_expr]. O primeiro caractere está na posição 1 | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Substitui todas as instâncias de [text_expr_1] em [text_expr_3] pelo valor [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Substitui todas as instâncias que correspondem à expressão regular [text_expr_1] em [text_expr_3] por [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
Para remover ou substituir todos os espaços em branco, incluindo espaços em branco Unicode, use esta sintaxe em vez de [\s]: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Delimita uma cadeia de caracteres por um caractere específico e seleciona um valor desse conjunto pelo índice. Isso extrai a enésima ocorrência de um padrão e a retorna. O primeiro elemento começa em 1. Se o índice estiver fora dos limites, a expressão retornará uma cadeia de caracteres vazia. | SPLIT_PART('Plan|1991|Tennis', '|', 2) =>'1991' SPLIT_PART('Plan|1991|Tennis', '/', 1) => 'Plan|1991|Tennis' SPLIT_PART('Plan|1991|Tennis', '/', 2) => '' |
TO_ACCOUNT_CODE([text_expr_1]) | Garante que o valor é compatível com o campo Código da conta de planejamento. Remove todos os espaços e substitui todos os caracteres não alfanuméricos por sublinhados (underscore) e trunca valores acima de 2.048 caracteres | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
Funções de data e hora
| ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Converte um valor de Texto em uma estrutura/formato conhecido em um valor de Data e hora. Somente determinados valores de [timestamp_format] são permitidos (confira abaixo) | CAST('2013-01-02' AS TIMESTAMP FROM 'yyyy-mm-dd') => TIMESTAMP '2013-01-02 00:00:00.000' |
TRUNCATE_TIMESTAMP([datetime_part] FROM [datetime_expr]) | Trunca data e hora [datetime_expr] em um calendário gregoriano [datetime_part] de ANO/MÊS/DIA/HORA | TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
Funções de data
| ||
CAST([text_expr] AS DATE FROM '[date_format]') | Converte um valor de Texto, com uma estrutura ou um formato conhecido, em um valor de Data. Somente determinados valores de [date_format] são permitidos (confira abaixo) | CAST('2013-01-02' AS DATE FROM 'yyyy-mm-dd') => DATE '2013-01-02 00:00:00.000' |
TRUNCATE_DATE([date_part] FROM [date_expr]) | Trunca a data [date_expr] para um calendário gregoriano [date_part] de ANO/MÊS/DIA/HORA | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Constantes de tempo
A integração tem suporte para duas constantes relacionadas à data e hora atual que podem ser usadas em expressões SQL:
Sintaxe | Descrição | Exemplo de uso |
|---|---|---|
CURRENT_TIMESTAMP | Fornece data e hora atual e pode ser usada em todos os locais em que os objetos Data e hora são usados | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Fornece a data atual e pode ser usada em todos os locais em que os objetos Data são usados | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
Instruções CASE
As instruções Case são usadas para escolher um valor com base em outros valores, como as declarações if em muitos idiomas
Sintaxe | Descrição | Exemplo de uso |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | Retorna [result_expr] da primeira [logic_expr] que retorna verdadeiro | CASE WHEN 1>2 THEN 'x' ELSE 'y' END => 'y' |
CASE [expr] WHEN [expr1] THEN [result_expr1] WHEN [expr#] THEN [result_expr#] ELSE [result_expr_def] END | Retorna [result_expr] da primeira [expr #] que é igual a [expr] | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
Instruções COALESCE
O Coalesce avalia os argumentos na ordem e retorna o primeiro valor não nulo de uma lista de argumentos definida. O Coalesce pode ser usado em uma coluna SQL, filtros SQL em carregadores e em expressões de junção. O Coalesce não pode ser usado no filtro de importação em uma tabela de preparação.
Sintaxe | Descrição | Exemplo de uso |
|---|---|---|
COALESCE ([expr]) | Retorna o primeiro valor não nulo em [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Expressões de relacionamentos de tabelas
Quando você usa itens de Relacionamento entre tabelas para unir tabelas, deve especificar uma Expressão de junção.
As tabelas que você quer unir podem conter colunas com o mesmo nome. Se houver colunas com o mesmo nome, você deverá diferenciá-las entre si usando:
- P para colunas da tabela principal. Exemplo: P."MyColumn"
- R para colunas de quaisquer outras tabelas. Exemplo: R."MyColumn"
Você pode usar estes valores de [timestamp_format] na função CAST ([text_expr] AS TIMESTAMP FROM '[timestamp_format]'):
- 'mon dd yyyy hh:mitt'
- 'mm/dd/yyyy'
- 'yyyy.mm.dd'
- 'yyyy-mm-dd'
- 'dd/mm/yyyy'
- 'dd.mm.yyyy'
- 'dd-mm-yyyy'
- 'dd mon yyyy'
- 'mon dd aaaa'
- 'mon dd yyyy hh:mi:ss:mmmmmmtt'
- 'mm-dd-yyyy'
- 'yyyy/mm/dd'
- 'yyyymmdd'
- 'dd mon yyyy hh:mi:ss:mmmmmm'
- 'yy-mm-dd hh:mi:ss'
- 'yy-mm-dd hh:mi:ss.mmmmmm'
- 'yy-mm-ddThh:mi:ss.mmmmmm'
- 'aaaamind'
- "dd-seg-aaaa"
- 'ddmmyyyy'
- 'dd/mm/yy'
- 'yy.mm.dd'
- 'dd/mm/yy'
- 'dd.mm.yy'
- "dd-mm-yy"
- 'dd seg. aa'
- "seg dd aa"
- "dd-mm-yy"
- 'yy/mm/dd'
- 'aaammdd'
Limitações específicas de fontes de dados no uso de filtro SQL na importação de dados e notas de uso
As limitações específicas de fontes de dados no uso de filtro SQL na importação de dados e as notas de uso são detalhadas a seguir.
- Tabelas de fontes de dados do NetSuite
- Quando você consulta o NetSuite diretamente (em vez de consultar os registros importados para a área de preparação do NetSuite), as expressões de filtro ficam limitadas aos recursos mostrados pelo NetSuite por meio dos serviços Web.
- Filtros de coluna simples com expressões de comparação e de lógica podem ser usados ao consultar o NetSuite.
- Filtros podem ser agrupados com a cláusula AND, mas não com a cláusula OR.
- Operadores (por exemplo, +, , /, *, $, ||) não podem ser usados.
- Funções escalares não podem ser usadas.
- Instruções Case não podem ser usadas.
- Para usar filtros em uma coluna personalizada, ela deve estar marcada para importação.
- Alguns filtros de coluna exigem que recursos NetSuite específicos sejam habilitados para que o filtro funcione.
- Algumas tabelas e colunas não têm suporte para filtros.
- Tabelas de fontes de dados de planilhas
- Quando você consulta um arquivo de planilha diretamente (em vez de consultar registros importados para a área de preparação de uma planilha), as expressões de filtro só pode especificar a "ID de carregamento" do arquivo a ser consultado. Se uma "ID de carregamento" não é especificada, são exibidos os dados do arquivo importado mais recentemente.
- Tabelas de fontes de dados JDBC
- Filtros de coluna simples com expressões de comparação e de lógica podem ser usados ao consultar fontes de dados JDBC.
- Operadores (por exemplo, +, , /, *, $, ||) não podem ser usados.
- Funções escalares não podem ser usadas.
- Instruções Case não podem ser usadas.
- Tabelas de fonte de dados do Salesforce
- Filtros de coluna simples com expressões de comparação e de lógica podem ser usados ao consultar o Salesforce.
- Operadores (por exemplo, +, , /, *, $, ||) não podem ser usados.
- Funções escalares não podem ser usadas.
- Instruções Case não podem ser usadas.
- Tabelas de fontes de dados do Intacct
- Filtros de coluna simples com expressões de comparação e de lógica podem ser usados ao consultar o Intacct. Isso inclui instruções IN(..), IS NULL, IS NOT NULL, LIKE e NOT LIKE.
- O Intacct não tem suporte para o operador <>. Em seu lugar, use a comparação NOT IN ().
- Operadores (por exemplo, +, , /, *, $, ||) não podem ser usados.
- Funções escalares não podem ser usadas.
- Instruções Case não podem ser usadas.
- Filtros em colunas de booliano devem usar as palavras-chave true/false, pois o Intacct não reconhece 1/0 como verdadeiro/falso.
- Tabelas de fonte de dados do Microsoft Dynamics GP
- Filtros de coluna simples com expressões de comparação e de lógica podem ser usados ao consultar o Microsoft Dynamics GP.
- Operadores (por exemplo, +, , /, *, $, ||) não podem ser usados.
- Funções escalares não podem ser usadas.
- Instruções Case não podem ser usadas.