Referencia: expresiones SQL
Las expresiones SQL se pueden usar con finalidades variadas en relación con la integración de datos. Las funciones del lenguaje SQL admitidas varían dependiendo del sistema en el que se vaya a ejecutar la instrucción SQL. Por ejemplo:
- Las consultas de datos que se han importado al almacenamiento provisional pueden hacer uso de todas las funciones del lenguaje SQL.
- Las consultas dirigidas a sistemas externos, como NetSuite, pueden hacer uso de filtros SQL y admitir un conjunto reducido de funciones.
Para consultar las limitaciones y notas de uso específicas de un tipo de origen de datos, consulte las siguientes secciones que aparecerán más adelante en este documento:
- Filtros SQL de NetSuite
- Filtros SQL de hoja de cálculo
- Filtros SQL de JDBC
- Filtros SQL de Salesforce
- Filtros SQL de Intacct
- Filtros SQL de Microsoft Dynamics GP
Solo admitimos un conjunto limitado de expresiones SQL. Admitimos otras operaciones de otras maneras dentro de Diseño de integraciones. Ejemplo: para crear combinaciones SQL, puede añadir tablas y columnas de combinaciones SQL arrastrando
Tabla combinada
de la carpeta Tabla personalizada
en Componentes de datos
al área de la tabla de un origen de datos.Valores literales
Constantes insertadas como valores fijos de fecha y hora o cadenas/números que no cambian en una expresión que utiliza valores literales.
Tipo de datos | Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|---|
Texto | '*****' | Cadena de texto rodeada de comillas simples. Para incluir una comilla simple en el texto, debe omitirse mediante una segunda comilla simple | 'Hace ''calor'' afuera' |
Valor entero | # | Introducido exactamente como debe aparecer | 999 |
Flotante | #.# | Siempre debe contener un punto (.) dentro del valor flotante, incluso si la parte fraccionaria es 0 | 7.7 |
Fecha y hora | TIMESTAMP '*****' | Palabra clave TIMESTAMP seguida de una representación de longitud constante entre comillas simples de la fecha con el formato yyyy-mm-dd hh:mm:ss.SSS
Puede especificar tres dígitos de precisión para fracciones de segundo. El máximo es 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' |
Fecha | DATE '*****' | Palabra clave DATE seguida de una representación de longitud constante entre comillas simples de la fecha con el formato yyyy-mm-dd | FECHA '1879-03-14' |
Booleano | TRUE (o FALSE) | Palabras clave TRUE o FALSE | FALSE |
Operadores
Los operadores numéricos permiten realizar operaciones matemáticas con expresiones, valores o columnas de carácter numérico (Valor entero, Flotante, Bit). Los operadores de texto combinan dos o más expresiones, valores o columnas.
Aplicable a | Operador | Descripción | Ejemplo de uso |
|---|---|---|---|
Números | + | Suma dos valores numéricos | MyNumericColumn + 1000 |
Números | - | Resta el valor del lado derecho al del lado izquierdo | MyNumericColumn 1000 |
Números | / | Divide el valor del lado izquierdo por el del lado derecho | MyNumericColumn / 1000 |
Números | * | Multiplica dos valores | MyNumericColumn * 1000 |
Texto | || | Concatena dos valores de texto | MyTextColumn || 'un sufijo' |
Comparación y expresiones lógicas
Estas expresiones dan como resultado 1 (verdadero) o 0 (falso) y se pueden utilizar en expresiones combinadas de tabla o como [expr] en comparaciones de CASE WHEN [expr] THEN [value] END. Las expresiones de comparación y de lógica pueden operar en columnas de texto, numéricas o de fecha y hora
Aplicable a | Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|---|
Cualquiera | = | Comprueba si dos valores o expresiones son iguales | MyColumn1 = MyColumn2 |
Cualquiera | <> | Comprueba si dos valores o expresiones no son iguales | MyColumn1 <> MyColumn2 |
Cualquiera | IS NULL | Comprueba si un valor o expresión es NULL. NULL no es equivalente a una cadena vacía | MyColumn1 IS NULL |
Cualquiera | IS NOT NULL | Comprueba si un valor o expresión da como resultado un valor que no es NULL | MyColumn1 IS NOT NULL |
Cualquiera | < | Comprueba si el valor o la expresión de la izquierda es menor que el valor o la expresión de la derecha | MyColumn1 < MyColumn2 |
Cualquiera | <= | Comprueba si el valor o la expresión de la izquierda es menor o igual que el valor o la expresión de la derecha | MyColumn1 <= MyColumn2 |
Cualquiera | > | Comprueba si el valor o la expresión de la izquierda es mayor que el valor o la expresión de la derecha | MyColumn1 > MyColumn2 |
Cualquiera | >= | Comprueba si el valor o la expresión de la izquierda es mayor o igual que el valor o la expresión de la derecha | MyColumn1 >= MyColumn2 |
Cualquiera | IN | Comprueba si un valor o una expresión están incluidos en un conjunto | MyColumn1 IN (1, 2, 3) |
Cualquiera | NOT IN | Comprueba si un valor o una expresión no están incluidos en un conjunto | MyColumn1 NOT IN (1, 2, 3) |
Texto | LIKE | Comprueba si un valor o una expresión de texto coincide con un patrón. El carácter % funciona como comodín | MyColumn1 LIKE '%Manzana' |
Texto | NOT LIKE | Comprueba si un valor o una expresión de texto no coincide con un patrón. El carácter % funciona como comodín | MyColumn1 NOT LIKE '%Manzana' |
Comparaciones | AND | Evalúa dos comparaciones y devuelve verdadero solo si ambas expresiones son verdaderas | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Comparaciones | OR | Evalúa dos comparaciones y devuelve verdadero si alguna expresión es verdadera | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Funciones escalares
Las funciones escalares toman valores de entrada y devuelven un solo valor
Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|
Funciones de valor de bit
| ||
CAST(expr AS BIT) | Convierte un valor de texto/valor flotante/valor entero en un valor de bit (0 o 1) | CAST('1' AS BIT) => 1 |
Funciones de valor entero
| ||
CAST(expr AS INTEGER) | Convierte un valor de texto/valor flotante/valor de bit en un valor entero | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Extrae el número correspondiente a (DAY) de [datepart] desde [datetime_expr1] hasta [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]) | Extrae el número correspondiente a (DAY) de [datepart] desde [date_expr1] hasta [date_expr2] | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Extrae [datepart] (DAY/MONTH/YEAR/HOUR/MINUTE/SECOND) de [datetime_expr] | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Extrae la longitud de la expresión textual [text_expr] | LENGTH('Hola') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Extrae el primer índice de [find_text_expr] en [search_text_expr]. El primer carácter es 1. | POSITION('ar' IN 'mar') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Extrae el primer índice de [find_text_expr] en [search_text_expr] después del índice de [start] (un [start] de -1 significa buscar el último). El primer carácter es 1. | POSITION('a' IN 'amar' FROM 1) => 4 |
Funciones de valor flotante
| ||
CAST(expr AS FLOAT) | Convierte un valor de texto/valor de bit/valor entero en un valor flotante | CAST('1.01' AS FLOAT) => 1.01 |
Funciones de valor de texto
| ||
CAST(expr AS NVARCHAR) | Convierte un valor flotante/valor entero/valor de bit en un valor de texto | CAST(1.01 AS NVARCHAR) => '1.01' |
TRIM([text_expr]) | Elimina los espacios iniciales y finales de [text_expr] | TRIM(' xxx ') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extrae una parte de la expresión textual [text_expr] desde la posición [start_int_expr]. El primer carácter está en la posición 1 | SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Extrae los caracteres de la expresión [len_int_expr] de [text_expr] desde la posición [start_int_expr]. El primer carácter está en la posición 1 | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Sustituye todas las instancias de [text_expr_1] que están en [text_expr_3] por el valor [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Sustituye todas las instancias que coinciden con la expresión regular [text_expr_1] que está en [text_expr_3] por el valor [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
Para eliminar o sustituir todos los espacios en blanco, incluidos los espacios en blanco Unicode, utilice esta sintaxis en lugar de [\s]: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Delimita una cadena por un carácter específico y selecciona un valor de los que están definidos por el índice. Esta función extrae la enésima aparición de un patrón y lo devuelve. El primer elemento comienza en 1. Si el índice está fuera de los límites, la expresión devuelve una cadena vacía. | 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]) | Garantiza que el valor es compatible con el campo Código de cuenta de planificación. Elimina todos los espacios y, a continuación, sustituye los caracteres no alfanuméricos por guiones bajos y trunca los valores de más de 2048 caracteres | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
Funciones de fecha y hora
| ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Convierte un valor de texto con una estructura o formato conocidos en un valor de fecha y hora. Solo se permiten determinados valores de [timestamp_format] (ver a continuación) | 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 el valor de fecha y hora [datetime_expr] a un calendario gregoriano [datetime_part] con formato DAY/MONTH/YEAR/HOUR | TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
Funciones de fecha
| ||
CAST([text_expr] AS DATE FROM '[date_format]') | Convierte un valor de texto con una estructura o formato conocidos en un valor de fecha. Solo se permiten determinados valores de [date_format] (ver a continuación) | 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 la fecha [date_expr] a un calendario gregoriano [date_part] con formato DAY/MONTH/YEAR/HOUR | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Constantes de tiempo
La integración admite dos constantes relacionadas con la fecha y hora actuales que se pueden usar en expresiones SQL:
Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|
CURRENT_TIMESTAMP | Proporciona la fecha y hora actuales y se puede usar en todos los lugares en los que se usen objetos de fecha y hora | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Proporciona la fecha actual y se puede usar en todos los lugares en los que se usen objetos de fecha | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
Declaraciones CASE
Las declaraciones de caso se utilizan para elegir un valor en función de otros valores, de manera muy similar a las declaraciones condicionales en muchos idiomas
Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | Se devuelve la expresión [result_expr] que se dé como resultado de la primera expresión [logic_expr] que devuelva el valor verdadero | 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 | Se devuelve la expresión [result_expr] que se dé como resultado de la primera expresión [expr #] que sea igual a [expr] | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
Declaraciones COALESCE
Coalesce evalúa los argumentos en orden y devuelve el primer valor no nulo de una lista de argumentos definida. Puede usarse en una columna SQL, filtros de SQL en cargadores y en expresiones combinadas. No puede usarse en el filtro de importación de una tabla de almacenamiento provisional.
Sintaxis | Descripción | Ejemplo de uso |
|---|---|---|
COALESCE ([expr]) | Devuelve el primer valor no nulo de una expresión [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Expresiones de relación de tabla
Cuando utiliza elementos de Relación de tabla para combinar tablas, debe especificar una Expresión de combinación.
Estas tablas que desea unir pueden contener columnas con el mismo nombre. Si existen columnas con el mismo nombre, debe diferenciarlas entre sí mediante:
- P para las columnas de la tabla principal. Ejemplo: P.“MyColumn”
- R para columnas de cualquier otra tabla. Ejemplo: R.“MyColumn”
Puede usar estos valores de [timestamp_format] en la función 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 yyyy'
- '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'
- 'yyyymondd'
- "yyyy-mon-dd"
- "ddmmaaaa"
- "dd/mm/aa"
- "aa.mm.dd"
- "dd/mm/aa"
- "dd.mm.aa"
- "dd-mm-aa"
- 'dd mes aa'
- 'mon dd aa'
- "mm-dd-aa"
- "aa/mm/dd"
- "aammdd"
Limitaciones y notas de uso SQL del filtro de importación de datos específicos del origen de datos
Las limitaciones y notas de uso SQL del filtro de importación de datos específicos del origen de datos se detallan a continuación.
- Tablas de origen de datos de NetSuite
- Al consultar NetSuite directamente (en lugar de consultar registros importados al almacenamiento provisional desde NetSuite), las expresiones de filtro se limitan a las capacidades expuestas por NetSuite a través de los servicios web.
- Se pueden usar filtros de columna simples con comparación y expresiones lógicas cuando se consulta NetSuite.
- Los filtros se pueden asociar con la opción AND, pero no con la opción OR.
- Los operadores (por ejemplo: +, /, *, $, ||) no se pueden utilizar.
- No se pueden utilizar funciones escalares.
- No se pueden utilizar declaraciones de caso.
- Para filtrar por una columna personalizada, esta debe estar marcada para importación.
- Algunos filtros de columna requieren la activación de funciones específicas de NetSuite para funcionar.
- Algunas tablas y columnas no admiten el filtrado.
- Tablas de origen de datos de hoja de cálculo
- Al consultar un archivo de hoja de cálculo directamente (en lugar de consultar registros importados al almacenamiento provisional desde una hoja de cálculo), la expresión de filtro solo puede especificar el "ID de carga" del archivo que se va a consultar. Si no se especifica un "ID de carga", se muestran los datos del último archivo importado.
- Tablas de origen de datos JDBC
- Se pueden usar filtros de columna simples con comparación y expresiones lógicas cuando se consultan orígenes de datos JDBC.
- Los operadores (por ejemplo: +, /, *, $, ||) no se pueden utilizar.
- No se pueden utilizar funciones escalares.
- No se pueden utilizar declaraciones de caso.
- Tablas de origen de datos de Salesforce
- Se pueden usar filtros de columna simples con comparación y expresiones lógicas cuando se consulta Salesforce.
- Los operadores (por ejemplo: +, /, *, $, ||) no se pueden utilizar.
- No se pueden utilizar funciones escalares.
- No se pueden utilizar declaraciones de caso.
- Tablas de origen de datos Intacct
- Se pueden usar filtros de columna simples con comparación y expresiones lógicas cuando se consulta Intacct. Esto incluye las declaraciones IN (..), IS NULL, IS NOT NULL, LIKE y NOT LIKE.
- Intacct no admite el operador <>, en su lugar, utilice la comparación NOT IN().
- Los operadores (por ejemplo: +, /, *, $, ||) no se pueden utilizar.
- No se pueden utilizar funciones escalares.
- No se pueden utilizar declaraciones de caso.
- Los filtros de columnas booleanas deben usar palabras clave de verdadero/falso, ya que Intacct no reconoce que 1/0 sea lo mismo que verdadero/falso.
- Tablas de origen de datos de Microsoft Dynamics GP
- Se pueden usar filtros de columna simples con comparación y expresiones lógicas cuando se consulta Microsoft Dynamics GP.
- Los operadores (por ejemplo: +, /, *, $, ||) no se pueden utilizar.
- No se pueden utilizar funciones escalares.
- No se pueden utilizar declaraciones de caso.