Riferimenti: Espressioni SQL
È possibile utilizzare le espressioni SQL per diversi scopi di integrazione dei dati. Le funzionalità del linguaggio SQL supportate variano a seconda del sistema nel quale verrà eseguita l'istruzione SQL. Ad esempio:
- Le query relative a dati importati nella gestione temporanea possono utilizzare tutte le funzionalità del linguaggio SQL.
- Le query dirette a sistemi esterni, come NetSuite, possono utilizzare filtri SQL e supportano un insieme di funzionalità limitato.
Per le limitazioni specifiche per i singoli tipi di origine dati e le note sull'utilizzo, consultare le seguenti sezioni più avanti in questo documento:
- Filtri SQL per NetSuite
- Filtri SQL per fogli di calcolo
- Filtri SQL per JDBC
- Filtri SQL per Salesforce
- Filtri SQL per Intacct
- Filtri SQL per Microsoft Dynamics GP
Il programma supporta un insieme limitato di espressioni SQL. Altre operazioni sono supportate con altre modalità all'interno di Progettazione integrazioni. Esempio: per creare JOIN SQL, è possibile aggiungere tabelle congiunte e colonne SQL trascinando
Tabella congiunta
dalla cartella Tabella personalizzata
di Componenti dati
nell'area della tabella di un'origine dati.Valori letterali
Costanti incorporate come i valori DateTime fissi oppure le stringhe e i numeri invariabili in un'espressione che utilizza valori letterali.
Tipo di dati | Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|---|
Testo | '*****' | Stringa di testo racchiusa tra virgolette singole. Per inserire una virgoletta singola nel testo (ad esempio in funzione di apostrofo), è necessario utilizzare una seconda virgoletta singola come carattere di escape. | 'L''estate sta iniziando' |
Intero | # | Valore immesso esattamente come deve essere visualizzato. | 999 |
A virgola mobile | #,# | Deve sempre contenere una virgola (,) all'interno del valore a virgola mobile, anche se il valore decimale è 0. | 7,7 |
Data/Ora | TIMESTAMP '*****' | Parola chiave TIMESTAMP, seguita da una rappresentazione di lunghezza costante della data, inclusa tra virgolette singole nel formato yyyy-mm-dd hh:mm:ss.SSS.
È possibile specificare 3 cifre di precisione per i secondi frazionari. Il valore massimo è di 6 cifre. | TIMESTAMP '09/11/1934 17:05:12.012345'
TIMESTAMP '02/01/2013 03:14:05.006006' TIMESTAMP '02/01/2013 03:14:05.006' TIMESTAMP '2013-01-02 03:14:05' |
Data | DATE '*****' | Parola chiave DATE seguita da una rappresentazione di lunghezza costante della data, inclusa tra virgolette singole nel formato yyyy-mm-dd. | DATA "14/03/1879" |
Booleano | TRUE (o FALSE) | Parola chiave TRUE o parola chiave FALSE. | FALSE |
Operatori
Gli operatori numerici eseguono operazioni matematiche su espressioni, valori o colonne di tipo numerico (intero, a virgola mobile, bit). Gli operatori di testo combinano due o più espressioni, valori o colonne di testo.
Applicabile a | Operatore | Descrizione | Esempio di utilizzo |
|---|---|---|---|
Numeri | + | Somma due valori numerici. | ColonnaNumerica + 1000 |
Numeri | - | Sottrae il valore a destra da quello a sinistra. | ColonnaNumerica - 1000 |
Numeri | / | Divide il valore a sinistra per quello a destra. | ColonnaNumerica/1000 |
Numeri | * | Moltiplica tra loro due valori. | ColonnaNumerica*1000 |
Testo | || | Concatena due valori di testo. | ColonnaDiTesto || 'suffisso' |
Espressioni di confronto e logiche
Queste espressioni restituiscono 1 (vero) o 0 (falso) e possono essere utilizzate nelle espressioni join delle tabelle o come [expr] nei confronti di tipo CASE WHEN [expr] THEN [value] END. Le espressioni di confronto e logiche possono essere utilizzate per le colonne di dati di tipo testo, numerico o DateTime.
Applicabile a | Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|---|
Qualsiasi | = | Verifica se due valori o due espressioni sono uguali. | Colonna1 = Colonna2 |
Qualsiasi | <> | Verifica se due valori o due espressioni non sono uguali. | Colonna1 <> Colonna2 |
Qualsiasi | IS NULL | Verifica se un valore o un'espressione è NULL. NULL non equivale a una stringa vuota. | Colonna1 IS NULL |
Qualsiasi | IS NOT NULL | Verifica se un valore o un'espressione si risolve in un valore diverso da NULL. | Colonna1 IS NOT NULL |
Qualsiasi | < | Verifica se il valore o l'espressione a sinistra è minore del valore o dell'espressione a destra. | Colonna1 < Colonna2 |
Qualsiasi | <= | Verifica se il valore o l'espressione a sinistra è minore o uguale al valore o all'espressione a destra. | Colonna1 <= Colonna2 |
Qualsiasi | > | Verifica se il valore o l'espressione a sinistra è maggiore del valore o dell'espressione a destra. | Colonna1 > Colonna2 |
Qualsiasi | >= | Verifica se il valore o l'espressione a sinistra è maggiore o uguale al valore o all'espressione a destra. | Colonna1 >= Colonna2 |
Qualsiasi | IN | Verifica se un'espressione o un valore è contenuto in un insieme. | Colonna1 IN (1, 2, 3) |
Qualsiasi | NOT IN | Verifica se un'espressione o un valore non è contenuto in un insieme. | Colonna1 NOT IN (1, 2, 3) |
Testo | LIKE | Verifica se un valore o un'espressione di testo corrisponde a uno schema. Il carattere % funge da carattere jolly. | Colonna1 LIKE '%Mela' |
Testo | NOT LIKE | Verifica se un valore o un'espressione di testo non corrisponde a uno schema. Il carattere % funge da carattere jolly. | Colonna1 NOT LIKE '%Mela' |
Confronti | AND | Valuta due confronti e restituisce Vero solo se entrambe le espressioni sono vere. | Colonna1 >= Colonna2 AND Colonna1 IN (1, 2, 3) |
Confronti | OR | Valuta due confronti e restituisce Vero se una delle due espressioni è vera. | (Colonna1 >= Colonna2) OR Colonna1 IN (1, 2, 3) |
Funzioni scalari
Le funzioni scalari accettano valori di input e restituiscono un singolo valore.
Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|
Funzioni bit | ||
CAST(expr AS BIT) | Converte un valore di testo/a virgola mobile/intero in un valore bit (0 o 1). | CAST('1' AS BIT) => 1 |
Funzioni per numeri interi | ||
CAST(expr AS INTEGER) | Converte un valore di testo/a virgola mobile/bit in un valore intero. | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Recupera il numero (DAY) di [datepart] da [datetime_expr1] a [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]) | Recupera il numero (DAY) di [datepart] da [date_expr1] a [date_expr2]. | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Recupera il valore (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) di [datepart] dal valore [datetime_expr]. | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Recupera la lunghezza di [text_expr]. | LENGTH('Salve') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Recupera il primo indice di [find_text_expr] in [search_text_expr]. Il primo carattere è 1. | POSITION('at' IN 'gatto') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Recupera il primo indice di [find_text_expr] in [search_text_expr] dopo l'indice [start] (se il valore di [start] è -1, recupera l'ultimo indice). Il primo carattere è 1. | POSITION('a' IN 'i gatti' FROM 1) => 4 |
Funzioni con virgola mobile | ||
CAST(expr AS FLOAT) | Converte un valore di testo/intero/bit in un valore a virgola mobile. | CAST('1,01' AS FLOAT) => 1,01 |
Funzioni di testo | ||
CAST(expr AS NVARCHAR) | Converte un valore a virgola mobile/intero/bit in un valore di testo. | CAST(1,01 AS NVARCHAR) => '1,01' |
TRIM([text_expr]) | Rimuove gli spazi iniziali e finali da [text_expr] | TRIM(' xxx ') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Estrae una parte di [text_expr] dalla posizione [start_int_expr]. Il primo carattere è nella posizione 1. | SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Estrae il numero di caratteri di [len_int_expr] da [text_expr] a partire dalla posizione [start_int_expr]. Il primo carattere è nella posizione 1. | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Sostituisce tutte le istanze di [text_expr_1] in [text_expr_3] con il valore [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Sostituisce tutte le istanze corrispondenti all'espressione regolare [text_expr_1] in [text_expr_3] con [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
Per rimuovere o sostituire tutti gli spazi vuoti, inclusi quelli Unicode, utilizzare questa sintassi anziché [\s]: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Delimita una stringa in base a un carattere specifico e seleziona un valore dell'insieme in base a un indice. Questa espressione estrae e restituisce l'occorrenza n di uno schema. Il primo elemento inizia da 1. Se l'indice non rientra nei limiti, l'espressione restituisce una stringa vuota. | SPLIT_PART('Piano|1991|Tennis', '|', 2) =>'1991' SPLIT_PART('Piano|1991|Tennis', '/', 1) => 'Piano|1991|Tennis' SPLIT_PART('Piano|1991|Tennis', '/', 2) => '' |
TO_ACCOUNT_CODE([text_expr_1]) | Garantisce che il valore sia compatibile con il campo Codice conto della pianificazione. Rimuove tutti gli spazi, quindi sostituisce tutti i caratteri non alfanumerici con caratteri di sottolineatura e tronca i valori di lunghezza superiore a 2048 caratteri. | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
Funzioni per valori DateTime | ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Converte un valore Testo con una struttura o un formato noto in un valore DateTime. Sono consentiti solo determinati valori per [timestamp_format] (vedere di seguito). | 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]) | Tronca il valore DateTime [datetime_expr] al valore [datetime_part] di un calendario gregoriano YEAR/MONTH/DAY/HOUR. | TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
Funzioni relative alla data | ||
CAST([text_expr] AS DATE FROM '[date_format]') | Converte un valore Testo con una struttura o un formato noto in un valore Data. Sono consentiti solo determinati valori [date_format] (vedere di seguito). | 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]) | Tronca il valore data [date_expr] al valore [date_part] di un calendario gregoriano YEAR/MONTH/DAY/HOUR. | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Costanti temporali
L'integrazione supporta due costanti relative alla data e ora correnti che possono essere utilizzate nelle espressioni SQL:
Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|
CURRENT_TIMESTAMP | Restituisce il valore DateTime corrente e può essere utilizzata ovunque vengono utilizzati oggetti di tipo DateTime. | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Restituisce la data corrente e può essere utilizzata ovunque vengono utilizzati gli oggetti di tipo data. | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
Istruzioni CASE
Le istruzioni CASE vengono utilizzate per scegliere un valore in base ad altri valori, in modo simile alle istruzioni IF in molti linguaggi.
Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | Restituisce il valore [result_expr] della prima [logic_expr] che restituisce Vero. | 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 | Restituisce il valore [result_expr] della prima [expr#] uguale a [expr]. | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
Istruzioni COALESCE
La funzione COALESCE valuta gli argomenti in ordine e restituisce il primo valore diverso da null di un elenco di argomenti definito. È possibile utilizzare COALESCE in una colonna SQL, nei filtri SQL dei caricatori e nelle espressioni join. Non è possibile utilizzare COALESCE nel filtro di importazione in una tabella di gestione temporanea.
Sintassi | Descrizione | Esempio di utilizzo |
|---|---|---|
COALESCE ([expr]) | Restituisce il primo valore diverso da null in [expr]. | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Espressioni di relazioni tra tabelle
Quando si utilizzano gli elementi Relazione tabella per unire le tabelle, è necessario specificare un'Espressione di join.
Le tabelle che si desidera unire potrebbero contenere colonne con lo stesso nome. Se esistono colonne con lo stesso nome, è necessario differenziare le colonne l'una dall'altra utilizzando:
- P per le colonne della tabella principale. Esempio: P."MyColumn"
- R per le colonne di qualsiasi altra tabella. Esempio: R."MyColumn"
È possibile utilizzare i seguenti valori [timestamp_format] nella funzione 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'
- 'aaaa-lun-gg'
- 'ggmmaaaa'
- 'gg/mm/aa'
- 'aa.mm.gg'
- 'gg/mm/aa'
- ‘dd.mm.yy’
- ‘dd-mm-yy’
- 'gg lun aa'
- 'lun gg aa'
- ‘mm-dd-yy’
- "aa/mm/gg"
- 'aammgg'
Limitazioni SQL del filtro di importazione dati con origini dati specifiche e note sull'utilizzo
Le limitazioni SQL del filtro di importazione dati con origini dati specifiche e le note sull'utilizzo sono riportate di seguito.
- Tabelle che utilizzano NetSuite come origine dati
- Quando si eseguono query direttamente in NetSuite (anziché nei record importati da NetSuite nella gestione temporanea), le espressioni di filtro sono limitate alle funzionalità di NetSuite disponibili tramite i servizi Web.
- Quando si eseguono query in NetSuite, è possibile utilizzare filtri di colonna semplici con espressioni di confronto e logiche.
- I filtri possono essere combinati con l'operatore AND, ma non con l'operatore OR.
- Non è possibile utilizzare gli altri operatori (ad esempio + , /, *, $, ||).
- Non è possibile utilizzare le funzioni scalari.
- Non è possibile utilizzare le istruzioni CASE.
- Per applicare un filtro in base a una colonna personalizzata, la colonna personalizzata deve essere contrassegnata per l'importazione.
- Per poter utilizzare alcuni filtri di colonna, è necessario che siano abilitate determinate funzionalità NetSuite.
- Alcune tabelle e colonne non supportano l'applicazione di filtri.
- Tabelle che utilizzano i fogli di calcolo come origine dati
- Quando si eseguono query direttamente su un foglio di calcolo (anziché sui record importati da un foglio di calcolo nella gestione temporanea), l'espressione di filtro può specificare solo l'ID di caricamento del file in cui eseguire la ricerca. Se non viene specificato un ID di caricamento, vengono visualizzati i dati dell'ultimo file importato.
- Tabelle che utilizzano JDBC come origine dati
- Quando si eseguono query nelle origini dati JDBC, è possibile utilizzare semplici filtri di colonna con espressioni di confronto e logiche.
- Non è possibile utilizzare gli altri operatori (ad esempio + , /, *, $, ||).
- Non è possibile utilizzare le funzioni scalari.
- Non è possibile utilizzare le istruzioni CASE.
- Tabelle che utilizzano Salesforce come origine dati
- Quando si eseguono query in Salesforce, è possibile utilizzare semplici filtri di colonna con espressioni di confronto e logiche.
- Non è possibile utilizzare gli altri operatori (ad esempio + , /, *, $, ||).
- Non è possibile utilizzare le funzioni scalari.
- Non è possibile utilizzare le istruzioni CASE.
- Tabelle che utilizzano Intacct come origine dati
- Quando si eseguono query in Intacct, è possibile utilizzare semplici filtri di colonna con espressioni di confronto e logiche. Sono incluse le istruzioni IN(..), IS NULL, IS NOT NULL, LIKE e NOT LIKE.
- Intacct non supporta l'operatore <>, ma è possibile utilizzare il confronto NOT IN().
- Non è possibile utilizzare gli altri operatori (ad esempio + , /, *, $, ||).
- Non è possibile utilizzare le funzioni scalari.
- Non è possibile utilizzare le istruzioni CASE.
- I filtri per a colonne di valori booleani devono utilizzare le parole chiave true/false, perché Intacct non riconosce i valori 1/0 come vero/falso.
- Tabelle che utilizzano Microsoft Dynamics GP come origine dati
- Quando si eseguono query in Microsoft Dynamics GP, è possibile utilizzare semplici filtri di colonna con espressioni di confronto e logiche.
- Non è possibile utilizzare gli altri operatori (ad esempio + , /, *, $, ||).
- Non è possibile utilizzare le funzioni scalari.
- Non è possibile utilizzare le istruzioni CASE.