Référence : expressions SQL
Vous pouvez utiliser des expressions SQL à diverses fins d’intégrations de 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 concernant des données importées dans la zone de stockage intermédiaires peuvent utiliser toutes les fonctions de langage SQL.
- Les requêtes adressées à des systèmes externes, tels que NetSuite, peuvent utiliser des filtres SQL et prendre en charge un ensemble réduit de fonctionnalités.
Pour les restrictions spécifiques au type de source de données et les notes d’utilisation, consultez les sections suivantes qui seront évoquées plus bas dans ce document :
- Filtres SQL NetSuite
- Filtres SQL de feuille de calcul
- Filtres SQL JDBC
- Filtres SQL Salesforce
- Filtres SQL Intacct
- Filtres SQL Microsoft Dynamics GP
Nous ne prenons en charge qu’un nombre limité d’expressions SQL. Nous prenons en charge d’autres opérations par d’autres moyens dans Concevoir des intégrations. Exemple : pour créer des jointures SQL, vous pouvez ajouter des tables de jointure et des colonnes SQL en faisant glisser
Table de jointure
à partir du dossier Table personnalisée
dans Composants de données
jusqu’à la zone des tables d’une source de données.Valeurs littérales
Constantes intégrées, telles que les 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 entourée de guillemets simples. Pour inclure une apostrophe dans le texte, elle doit être doublée d’une autre apostrophe | 'Qu’’il fait chaud dehors' |
Entier | # | Saisi exactement comme il doit apparaître | 999 |
Flottant | #,# | Doit toujours contenir une virgule (,) dans la valeur flottante, même si la partie décimale est 0 | 7,7 |
DateTime | TIMESTAMP ’*****’ | Mot clé TIMESTAMP suivi d'un guillemet simple, longueur constante, représentation de la date au format yyyy-mm-dd hh:mm:ss.SSS
Vous pouvez préciser une précision de trois chiffres pour les fractions de seconde. Le nombre maximum est de 6 chiffres. | 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' |
Date | DATE ’*****’ | Mot clé DATE suivi d'un guillemet simple, de longueur constante, qui représente la représentation du format de date aaaa-mm-jj | DATE '1879-03-14' |
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 (Integer, Float, 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 à celle 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 génèrent la valeur 1 (True) ou 0 (False) 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 être utilisées dans les colonnes Text, Numeric ou DateTime.
S’applique à | Syntaxe | Description | Exemple d’utilisation |
|---|---|---|---|
Indifférent | = | Vérifie si deux valeurs ou expressions sont égales | MyColumn1 = MyColumn2 |
Indifférent | <> | Vérifie si deux valeurs ou expressions ne sont pas égales | MyColumn1 <> MyColumn2 |
Indifférent | IS NULL | Vérifie si une valeur ou expression est NULL. NULL n’est pas équivalent à une chaîne vide | MyColumn1 IS NULL |
Indifférent | IS NOT NULL | Vérifie si une valeur ou expression génère une valeur nonNULL | MyColumn1 IS NOT NULL |
Indifférent | < | Vérifie si la valeur ou l’expression de gauche est inférieure à la valeur ou à l’expression de droite | MyColumn1 < MyColumn2 |
Indifférent | <= | Vérifie si la valeur ou l’expression de gauche est inférieure ou égale à la valeur ou à l’expression de droite | MyColumn1 <= MyColumn2 |
Indifférent | > | Vérifie si la valeur ou l’expression de gauche est supérieure à la valeur ou à l’expression de droite | MyColumn1 > MyColumn2 |
Indifférent | >= | Vérifie si la valeur ou l’expression de gauche est supérieure ou égale à la valeur ou à l’expression de droite | MyColumn1 >= MyColumn2 |
Indifférent | IN | Vérifie si une valeur ou expression est contenue dans un ensemble | MyColumn1 IN (1, 2, 3) |
Indifférent | 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 à un modèle. Le fonctionnement du caractère % est identique à celui d’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 tendances. Le fonctionnement du caractère % est identique à celui d’un caractère générique. | MyColumn1 NOT LIKE ’%Apple’ |
Comparaisons | AND | Évalue deux comparaisons et renverra la valeur True uniquement si les deux expressions sont vraies | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Comparaisons | OR | Évalue deux comparaisons et renverra la valeur True si l’expression est vraie | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Fonctions scalaires
Les fonctions scalaires utilisent des valeurs d’entrée et renvoient une valeur unique.
Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
Fonctions Bit
| ||
CAST(expr AS BIT) | Convertit une valeur Text/Float/Integer en valeur Bit (0 ou 1) | CAST(’1’ AS BIT) => 1 |
Fonctions Integer
| ||
CAST(expr AS INTEGER) | Convertit une valeur Text/Float/Bit en valeur Integer | CAST(’2’ AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Extrait le nombre de (DAY) [datepart] de l’expression [datetime_expr1] vers l’expression [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]) | Extrait le nombre de (DAY) [datepart] de l’expression [date_expr1] vers l’expression [date_expr2] | DATEDIFF(DAY FROM DATE ’2013-02-01’ TO DATE ’2013-02-10’) => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Extrait [datepart] (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) de l’expression [datetime_expr] | EXTRACT(MONTH FROM DATE ’2013-02-01’) => 2 |
LENGTH([text_expr]) | Extrait la longueur de l’expression [text_expr] | LENGTH(’Hello’) => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Extrait le premier index de l’expression [find_text_expr] dans l’expression [search_text_expr]. Le premier caractère est 1. | POSITION(’at’ IN ’hat’) => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Extrait le premier index de l’expression [find_text_expr] dans [search_text_expr] après l’index [start] (une valeur [start] de -1 signifie "trouver le dernier"). Le premier caractère est 1. | POSITION(’a’ IN ’a hat’ FROM 1) => 4 |
Fonctions Flottante
| ||
CAST(expr AS FLOAT) | Convertit une valeur Text/Integer/Bit en valeur Float | CAST(’1.01’ AS FLOAT) => 1.01 |
Fonctions Text
| ||
CAST(expr AS NVARCHAR) | Convertit une valeur Float/Integer/Bit en valeur Texte | CAST(1.01 AS NVARCHAR) => ’1.01’ |
TRIM([text_expr]) | Supprime les espaces de début ou de fin de l’expression [text_expr] | TRIM(’ xxx ’) => ’xxx’ |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extrait une partie de l’expression [text_expr] de la position de l’expression [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 l’expression [text_expr] en 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]) | Remplace toutes les instances de [text_expr_1] dans l’expression [text_expr_3] par 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]) | Remplace toutes les instances correspondant à l’expression standard [text_expr_1] dans [text_expr_3] par [text_expr_2]. | REGEX_REplace('[0-9]' with 'a' IN 'a7b5c') => 'aaBAC'
Pour retirer ou remplacer tous les espaces blancs, y compris les espaces blancs Unicode, utilisez la syntaxe suivante au lieu de [\s] : [\u0009\u0020\u00A0\u1680\u2000-\u200A\u205F\u3000] |
SPLIT_Part([chaîne],[delimiter],[part]) | Délimitez une chaîne par un caractère spécifique et sélectionnez une valeur issue de cet ensemble définie par l’index, ce qui permet d’extraire la énième occurrence d’une tendance et la retourne. Le premier élément commence à 1. Si l’index est hors limites, l’expression renverra 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]) | S’assure que la valeur est compatible avec le champ Code du compte Planning. 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. | TO_ACCOUNT_CODE(’A - 860+’) => ’A_860’ |
Fonctions DateTime
| ||
CAST([text_expr] AS TIMESTAMP FROM ’[timestamp_format]’) | Convertit une valeur Text avec une structure/un format connu en valeur DateTime. Seules certaines valeurs [timestamp_format] sont autorisées (voir ci-dessous). | 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]) | Tronque DateTime [datetime_expr] en calendrier grégorien [datetime_part] de 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 Date
| ||
CAST([text_expr] AS DATE FROM ’[date_format]’) | Convertit une valeur Texte avec une structure/un format connu en valeur Date. Seules certaines valeurs [date_format] sont autorisées (voir ci-dessous). | CAST(’2013-01-02’ AS DATE FROM ’dd-mm-yyyy’) => DATE ’2013-01-02 00:00:00.000’ |
TRUNCATE_DATE([date_part] FROM [date_expr]) | Tronque Date [date_expr] en un calendrier grégorien [date_part] de YEAR/MONTH/DAY/HOUR | TRUNCATE_DATE(MONTH FROM DATE ’2013-11-22’) => DATE ’2013-11-01 00:00:00.000’ |
Constantes de temps
Intégration prend en charge deux constantes liées à la date et à l’heure actuelles et qui peuvent être utilisées dans les expressions SQL suivantes :
Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
CURRENT_TIMESTAMP | Donne la date/heure actuelle et peut être utilisée partout où les objets DateTime sont utilisés | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Donne la date actuelle et peut être utilisée partout où les objets Date sont utilisés | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
Instructions CASE
Les instructions Case permettent de choisir une valeur en fonction d’autres valeurs, un peu comme les conditions IF dans de nombreux langages.
Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | L’expression [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 figurant dans des chargeurs et dans des expressions de jointure. Coalesce ne peut pas être utilisé dans le filtre d’importation d’une table intermédiaire.
Syntaxe | Description | Exemple d’utilisation |
|---|---|---|
COALESCE([expr]) | Renvoie le premier non nul dans [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Expressions des relations entre tables
Quand vous utilisez des éléments de lien de table pour joindre des tables, vous devez préciser une expression de jointure.
Les tables que vous souhaitez joindre peuvent contenir des colonnes portant le même nom. S’il existe des colonnes avec le même nom, vous devez différencier les colonnes en utilisant :
- P pour les colonnes du tableau principal. Exemple : P."MyColumn"
- R pour les colonnes de toute autre table. Exemple : R."MyColumn"
Vous pouvez utiliser ces valeurs de type [timestamp_format] dans la fonction CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') :
- 'lun jj 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'
- 'aaaaaaaaaaaaaaa
- 'yyyy-lu-dd'
- ‘ddmmyyyy’
- 'mm/jj/aa'
- ‘yy.mm.dd’
- « jj/mm/aa »
- 'jj.mm.aa'
- 'jj-mm-aa'
- « jj mon aa »
- 'lun jj yy'
- 'mm-jj-aa'
- 'aa/mm/jj'
- 'ymmdd'
Limitations et remarques sur l'utilisation du filtre SQL pour l'importation de données propres à une source de données
Les restrictions SQL et notes d’utilisation des filtres d’importation de données spécifiques à une source de données sont détaillées ci-dessous.
- Tables des sources de données NetSuite
- Lorsque vous interrogez directement NetSuite (plutôt que d’interroger des enregistrements importés dans la zone de stockage intermédiaire depuis NetSuite), les expressions de filtres sont limitées aux fonctionnalité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 lorsque vous interrogez NetSuite.
- Les filtres peuvent être traités ensemble avec l’opérateur AND, mais pas avec l’opérateur OR.
- Des opérateurs (exemple : +, , /, *, $, ||) 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 une colonne personnalisée, la colonne personnalisée doit être marquée pour l’importation.
- Le fonctionnement de certains filtres de colonne nécessite l’activation de fonctions NetSuite spécifiques.
- Certaines tables et colonnes ne prennent pas en charge le filtrage.
- Tables de sources de données de type feuille de calcul
- Lorsque vous interrogez un fichier de feuille de calcul directement (contrairement à l’interrogation d’enregistrements importés dans la table intermédiaire depuis une feuille de calcul), l’expression de filtre peut uniquement indiquer l’identifiant du téléchargement du fichier à interroger. Si aucun "identifiant de téléchargement" n’est indiqué, les données du fichier importé le plus récemment vont s’afficher.
- 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 de sources de données JDBC.
- Des opérateurs (exemple : +, , /, *, $, ||) 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 sources de données Salesforce
- Des filtres de colonne simples avec des expressions de comparaison et logiques peuvent être utilisés lorsque vous interrogez Salesforce.
- Des opérateurs (exemple : +, , /, *, $, ||) 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 lorsque vous interrogez Intacct, ce qui permet d’inclure les instructions IN(..), IS NULL, IS NOT NULL, LIKE et NOT LIKE.
- Intacct ne prend pas en charge l’opérateur <>, utilisez plutôt la comparaison NOT IN().
- Des opérateurs (exemple : +, , /, *, $, ||) 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 lorsque vous interrogez Microsoft Dynamics GP.
- Des opérateurs (exemple : +, , /, *, $, ||) ne peuvent pas être utilisés.
- Les fonctions scalaires ne peuvent pas être utilisées.
- Les instructions Case ne peuvent pas être utilisées.