Référence d’expression SQL
Vous pouvez utiliser des expressions SQL une variété d’objectifs d’intégration des données. Les fonctions de langage SQL prises en charge varient selon le système sur lequel l'instruction SQL sera exécutée. Par exemple :
- Les requêtes des données importées dans la zone de stockage peuvent utiliser toutes les fonctions de langage SQL.
- Les requêtes adressées aux systèmes externes, comme NetSuite, peuvent utiliser des filtres SQL et prendre en charge un ensemble réduit de fonctionnalités.
Pour les limitations spécifiques au type de source de données et les notes d’utilisation, suivez les liens ci-dessous :
- NetSuite Filtres SQL
- Filtres SQL de feuille de calcul
- JDBC Filtres SQL
- Salesforce Filtres SQL
- Intacct Filtres SQL
- Microsoft Dynamics GP Filtres SQL
Valeurs littérales
Les constantes incorporées telles que des valeurs DateTime fixes ou des chaînes/nombres non changeants dans une expression utilisant des valeurs littérales.
| Type de données | Syntaxe | Description | Exemple d’utilisation |
|---|---|---|---|
| Texte | '*****' | Chaîne de texte entre guillemets simples. Pour inclure une apostrophe dans le texte, elle doit être doublée en utilisant un guillemet |
'It''s hot outside' |
| Entier | # | Saisi exactement comme il doit apparaître | 999 |
| Flottant | #.# | Doit toujours contenir un point (.) dans la valeur flottante, même si la partie décimale est 0 | 7.7 |
| Date et heure | TIMESTAMP '*****' | Mot-clé TIMESTAMP suivi d’une représentation de la date, de longueur constante, entre guillemets simples format aaaa-mm-jj hh:mm:ss.SSS |
TIMESTAMP '20130102 03:04:05.006' |
| Date | DATE '*****' | Mot-clé DATE suivi d’une représentation de la date, de longueur constante, entre des guillemets simples format aaaa-mm-jj |
DATE '20130102' |
| Booléen | TRUE (ou FALSE) |
Mot-clé TRUE ou mot-clé FALSE | FALSE |
Opérateurs
Les opérateurs numériques effectuent des opérations mathématiques sur des expressions, des valeurs ou des colonnes numériques (Entier, Flottante, Bit). Les opérateurs de texte combinent deux ou plusieurs expressions, valeurs ou colonnes Texte.
| S’applique à | Opérateur | Description | Exemple d’utilisation |
|---|---|---|---|
| Nombres | + | Ajoute deux valeurs numériques ensemble | MyNumericColumn + 1000 |
| Nombres | - | Soustrait la valeur du côté droit du côté gauche | MyNumericColumn 1000 |
| Nombres | / | Divise le côté gauche par le côté droit | MyNumericColumn / 1000 |
| Nombres | * | Multiplie deux valeurs ensemble | MyNumericColumn * 1000 |
| Texte | || | Concatène deux valeurs de texte ensemble | MyTextColumn || ' un suffixe' |
Expressions logiques et de comparaison
Ces expressions se résolvent sur 1 (true - vrai) ou 0 (false - faux) et peuvent être utilisées dans des expressions de jointure de table ou comme [expr] dans les comparaisons CASE WHEN [expr] THEN [value] END Les expressions de comparaison et logiques peuvent fonctionner sur les colonnes Texte, Numérique ou Date
| S’applique à | Syntaxe | Description | Exemple d’utilisation |
|---|---|---|---|
| N’importe quel(le) | = | Vérifie si deux valeurs ou expressions sont égales | MyColumn1 = MyColumn2 |
| N’importe quel(le) | <> | Vérifie si deux valeurs ou expressions ne sont pas égales | MyColumn1 <> MyColumn2 |
| N’importe quel(le) | IS NULL | Vérifie si une valeur ou expression est NULL. NULL n’est pas équivalent à une chaîne vide | MyColumn1 IS NULL |
| N’importe quel(le) | IS NOT NULL | Vérifie si une valeur ou expression est résolue en une valeur nonNULL |
MyColumn1 IS NOT NULL |
| N’importe quel(le) | < | Vérifie si la valeur ou l’expression de gauche est inférieure à la valeur ou à l’expression de droite | MyColumn1 < MyColumn2 |
| N’importe quel(le) | <= | Vérifie si la valeur ou l’expression de gauche est inférieure ou égale à la valeur ou à l’expression de droite | MyColumn1 <= MyColumn2 |
| N’importe quel(le) | > | Vérifie si la valeur ou l’expression de gauche est supérieure à la valeur ou à l’expression de droite | MyColumn1 > MyColumn2 |
| N’importe quel(le) | >= | Vérifie si la valeur ou l’expression de gauche est supérieure ou égale à la valeur de droite ou expression |
MyColumn1 >= MyColumn2 |
| N’importe quel(le) | IN | Vérifie si une valeur ou expression est contenue dans un ensemble | MyColumn1 IN (1, 2, 3) |
| N’importe quel(le) | NOT IN | Vérifie si une valeur ou expression n’est pas contenue dans un ensemble | MyColumn1 NOT IN (1, 2, 3) |
| Texte | LIKE | Vérifie si une valeur ou expression de texte correspond à une tendance. Le caractère % fonctionne comme un caractère générique |
MyColumn1 LIKE '%Apple' |
| Texte | NOT LIKE | Vérifie si une valeur ou expression de texte ne correspond pas à une tendance. Le caractère % fonctionne comme caractère générique | MyColumn1 NOT LIKE '%Apple' |
| Comparaisons | AND | Évalue deux comparaisons et retourne true (vrai) uniquement si les deux expressions sont vraies | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
| Comparaisons | OR | Évalue deux comparaisons et retourne true (vrai) si l’expression est vraie | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Fonctions scalaires
Les fonctions scalaires prennent des valeurs d’entrée et retournent une valeur unique
| Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
| Fonctions Bit | ||
| CAST(expr AS BIT) | Convertit une valeur Texte/Flottante/Entier en valeur Bit (0 ou 1) | CAST('1' AS BIT) => 1 |
| Fonctions Entier | ||
| CAST(expr AS INTEGER) | Convertit une valeur Texte/Flottante/Bit en valeur Entier | CAST('2' AS INTEGER) => 2 |
| TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Récupère le nombre de [datepart]'s (DAY) de [datetime_expr1] to [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]) | Récupère le nombre de [datepart]'s (DAY) de [date_expr1] to [date_expr2] |
DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
| EXTRACT([datepart] FROM [datetime_expr]) | Récupère le [datepart] (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) de la [datetime_expr] | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
| LENGTH([text_expr]) | Récupère la longueur de l'expression [text_expr] | LENGTH('Hello') => 5 |
| POSITION([find_text_expr] IN [search_text_expr]) | Récupère le premier index de [find_text_expr] dans la [search_text_expr]. Le premier caractère est 1. |
POSITION('at' IN 'hat') => 2 |
| POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Récupère le premier index de [find_text_expr] dans la [search_text_expr] après l'index [start] (un [start] de 1 signifie trouver la dernière). Le premier caractère est 1. |
POSITION('a' IN 'a hat' FROM 1) => 4 |
| Fonctions à valeur flottante | ||
| CAST(expr AS FLOAT) | Convertit une valeur Texte/Entier/Bit en valeur Flottante | CAST('1.01' AS FLOAT) => 1.01 |
| Fonctions texte | ||
| CAST(expr AS NVARCHAR) | Convertit une valeur Flottante/Entier/Bit en valeur Texte | CAST(1.01 AS NVARCHAR) => '1.01' |
| TRIM([text_expr]) | Supprime les espaces avant ou après la [text_expr] | TRIM(' xxx ') => 'xxx' |
| SUBSTRING([text_expr] FROM [start_int_expr]) |
Extrait une partie de la [text_expr] de la position [start_int_expr]. Le premier caractère est à la position 1 |
SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
| SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) |
Extrait les caractères [len_int_expr] de la [text_expr] de la position [start_int_expr]. Le premier caractère est à la position 1 | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
|
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) uniquement disponible pour les colonnes SQL personnalisées |
Remplace toutes les instances de [text_expr_1] dans [text_expr_3] avec la valeur [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
|
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) uniquement disponible pour les colonnes SQL personnalisées |
Remplace toutes les instances qui correspondent à l’expression régulière [text_expr_1] dans [text_expr_3] par [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac' |
|
SPLIT_PART([string],[delimiter],[part]) uniquement disponible pour les colonnes SQL personnalisées |
Délimitez une chaîne par un caractère spécifique et sélectionnez une valeur de cet ensemble définie par index. Cela extrait la énième occurrence d’une tendance et la retourne. Le premier élément commence à 1. Si l’index est hors limites, l’expression retourne une chaîne vide. | 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]) uniquement disponible pour les colonnes SQL personnalisées |
S’assure que la valeur est compatible avec le champ Code de compte de planification. Supprime tous les espaces, puis remplace tous les caractères non alphanumériques par des tirets bas et tronque les valeurs de plus de 2 048 caractères de long | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
| Fonctions DateTime (Date et heure) | ||
| CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') |
Convertit une valeur Texte avec une structure/un format connu en une valeur DateTime. Seules certaines valeurs [timestamp_format] sont autorisées (voir ci-dessous) | CAST('20130102' AS TIMESTAMP FROM 'yyyy-mm-dd') => TIMESTAMP '2013-01-02 00:00:00.000' |
| TRUNCATE_TIMESTAMP([datetime_part] FROM [datetime_expr]) |
Tronque DateTime [datetime_expr] en une [datetime_part] de calendrier grégorien au format YEAR/MONTH/DAY/HOUR |
TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
| Fonctions de date | ||
| CAST([text_expr] AS DATE FROM '[date_format]') |
Convertit une valeur Texte avec une structure/un format connu en une valeur Date. Seules certaines valeurs [date_format] sont autorisées (voir ci-dessous) |
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]) |
Tronque Date [date_expr] en une [date_part] du calendrier grégorien au format YEAR/MONTH/DAY/HOUR |
TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Constantes de temps
Adaptive Integration prend en charge deux constantes liées à la date et l’heure actuelles qui peuvent être utilisées dans les expressions SQL :
| Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
| CURRENT_TIMESTAMP | Donne la date/heure DateTime et peut être utilisé partout où les objets DateTime sont utilisés | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
| CURRENT_DATE | Donne la date actuelle et peut être utilisé partout où les objets Date sont utilisés | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
Instruction CASE
Les instructions Case sont utilisées pour choisir une valeur en fonction d’autres valeurs, un peu comme les conditions IF dans de nombreuses langages
| Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
| CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | La [result_expr] de la première [logic_expr] qui renvoie une valeur true est renvoyée |
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 | L'expression [result_expr] de la première expression [expr#] égale à [expr] est renvoyée |
CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
Instructions COALESCE
Coalesce évalue les arguments dans l’ordre et renvoie la première valeur non nulle à partir d’une liste d’arguments définie. Coalesce peut être utilisé dans une colonne SQL, des filtres SQL dans des chargeurs et dans des expressions de jointure. Coalesce ne peut pas être utilisé dans le filtre d’importation d’une table de stockage.
| Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
| COALESCE ([expr]) | Renvoie le premier non nul dans [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Expressions de relation entre tables
Lorsque vous joignez des tables à l’aide des éléments « Relation entre tables », une expression de jointure doit être spécifiée. Il est possible que les deux tables jointes comportent des colonnes portant le même nom. Dans de tels cas, les colonnes de la table principale peuvent être qualifiées en utilisant 'P.' (p. ex. P."MyColumn") et la table associée en utilisant 'R.' (par ex. R."MyColumn")
La table suivante détaille les valeurs [timestamp_format] pouvant être utilisées dans la fonction CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]')
| Format |
|---|
| 'mon dd yyyy hh:mitt' |
| 'mm/dd/yyyy' |
| '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' |
Limites SQL et notes d’utilisation de filtre d’importation de données spécifiques à la source de données
Les limites SQL de filtre d’importation de données spécifiques à la source de données et les notes d’utilisation sont détaillées ci-dessous.
Tables de source de données NetSuite
Lors de l’interrogation de NetSuite directement (plutôt que de demander des enregistrements importés dans la zone de stockage depuis NetSuite) les expressions de filtre sont limitées aux capacités exposées par NetSuite via les services Web.
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lors des requêtes NetSuite.
- Les filtres peuvent être traités ensemble avec l’opérateur AND, mais pas avec l’opérateur OR.
- Des opérateurs (par ex., , , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.
- Pour filtrer sur une colonne personnalisée, la colonne personnalisée doit être marquée pour l’importation.
- Certains filtres de colonne nécessitent l’activation de fonctions NetSuite spécifiques pour que le filtre fonctionne.
- Certaines tables et certaines colonnes ne prennent pas en charge le filtrage.
Tables de source de données de feuille de calcul
Lorsque vous interrogez un fichier de feuille de calcul directement (contrairement à l’interrogation d’enregistrements importés dans la table de stockage depuis une feuille de calcul), l’expression de filtre peut uniquement spécifier l’"ID de téléchargement" du fichier à interroger. Si un "ID de téléchargement" n’est pas spécifié, les données du fichier importé le plus récemment sont affichées.
Tables de source de données JDBC
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lors de l’interrogation des sources de données JDBC .
- Des opérateurs (par ex., , , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.
Tables de source de données Salesforce
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lors des requêtes Salesforce.
- Des opérateurs (par ex., , , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.
Tables de source de données Intacct
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lors des requêtes Intacct. Ceci inclut les expressions IN(..), IS NULL, IS NOT NULL, LIKE and NOT LIKE.
- Intacct ne prend pas en charge l’opérateur <>, utilisez plutôt la comparaison NOT IN().
- Des opérateurs (par ex., , , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.
- Les filtres relatifs aux colonnes booléennes doivent utiliser les mots-clés true/false car Intacct ne reconnaît pas 1/0 comme étant similaire à true/false.
Tables de source de données Microsoft Dynamics GP
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lors des requêtes Microsoft Dynamics GP.
- Des opérateurs (par ex., , , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.