Referenz: SQL-Ausdrücke
Sie können SQL-Ausdrücke für verschiedene Datenintegrationszwecke verwenden. Die unterstützten SQL-Sprach-Features variieren je nach dem System, für das die SQL-Anweisung ausgeführt wird. Beispiel:
- Für Abfragen von Daten, die in das Staging importiert wurden, können alle SQL-Sprach-Features verwendet werden.
- Für Abfragen an externe Systeme wie NetSuite können SQL-Filter verwendet werden und es wird eine begrenzte Anzahl von Features unterstützt.
Informationen zu Beschränkungen für bestimmte Arten von Datenquellen und Hinweise zur Verwendung finden Sie in den folgenden Abschnitten dieses Dokuments:
- SQL-Filter für NetSuite
- SQL-Filter für Tabellen
- SQL-Filter für JDBC
- SQL-Filter für Salesforce
- SQL-Filter für Intacct
- SQL-Filter für Microsoft Dynamics GP
Es wird nur eine begrenzte Anzahl von SQL-Ausdrücken unterstützt. Andere Vorgänge werden im Rahmen von Integrationsdesign auf andere Weise unterstützt. Beispiel: Um SQL JOINs zu erstellen, können Sie SQL-Join-Tabellen und -Spalten hinzufügen, indem Sie
Join-Tabelle
aus dem Ordner Benutzerdefinierte Tabelle
in Datenkomponenten
in den Tabellenbereich einer Datenquelle ziehen.Literalwerte
Eingebettete Konstanten wie feste DateTime-Werte oder unveränderliche Zeichenfolgen/Zahlen in einem Ausdruck verwenden Literalwerte.
Datentyp | Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|---|
Text | '*****' | Textzeichenfolge in einfachen Anführungszeichen. Um ein einfaches Anführungszeichen im Text zu verwenden, muss es mit einem zweiten einfachen Anführungszeichen maskiert werden. | 'Wer''s glaubt' |
Integer | # | Eine Ganzzahl, die genau so eingegeben wird, wie sie angezeigt werden soll. | 999 |
Float | #.# | Ein Float-Wert sollte immer einen Punkt (.) enthalten, auch wenn der Bruchteil 0 ist. | 7.7 |
DateTime | TIMESTAMP '*****' | Auf das Schlüsselwort „TIMESTAMP“ folgt eine in einfache Anführungszeichen gesetzte Darstellung des Datums mit konstanter Länge im Format yyyy-mm-dd hh:mm:ss.SSS.
Sie können für Sekundenbruchteile 3 Stellen als Genauigkeit angeben. Es sind maximal 6 Stellen zulässig. | 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 '*****' | Auf das Schlüsselwort „DATE“ folgt eine in einfache Anführungszeichen gesetzte Darstellung des Datums im Format yyyy-mm-dd. | DATE '14.03.1879' |
Boolesch | TRUE (oder FALSE) | Schlüsselwort „TRUE“ oder Schlüsselwort „FALSE“ | FALSE |
Operatoren
Numerische Operatoren führen mathematische Operationen mit numerischen Ausdrücken, Werten oder Spalten aus (Integer, Float, Bit). Textoperatoren verbinden zwei oder mehr Textausdrücke, -werte oder -spalten.
Gilt für | Operator | Beschreibung | Verwendungsbeispiel |
|---|---|---|---|
Zahlen | + | Addiert zwei numerische Werte | MyNumericColumn + 1000 |
Zahlen | – | Subtrahiert den rechten Wert von dem auf der linken Seite | MyNumericColumn – 1000 |
Zahlen | / | Dividiert die linke Seite durch die rechte Seite | MyNumericColumn / 1000 |
Zahlen | * | Multipliziert zwei Werte | MyNumericColumn * 1000 |
Text | || | Verkettet zwei Textwerte | MyTextColumn || ' ein Suffix' |
Vergleichsausdrücke und logische Ausdrücke
Diese Ausdrücke werden zu 1 (wahr) oder 0 (falsch) aufgelöst und können in Join-Ausdrücken für Tabellen oder als „[expr]“ in Vergleichen der Art „CASE WHEN [expr] THEN [value] END“ verwendet werden. Vergleichsausdrücke und logische Ausdrücke können für Text-, numerische oder DateTime-Spalten verwendet werden.
Gilt für | Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|---|
Beliebig | = | Prüft, ob zwei Werte oder Ausdrücke gleich sind | MyColumn1 = MyColumn2 |
Beliebig | <> | Prüft, ob zwei Werte oder Ausdrücke ungleich sind | MyColumn1 <> MyColumn2 |
Beliebig | IS NULL | Prüft, ob ein Wert oder Ausdruck NULL ist. NULL ist nicht gleichbedeutend mit einer leeren Zeichenfolge. | MyColumn1 IS NULL |
Beliebig | IS NOT NULL | Prüft, ob ein Wert oder Ausdruck zu ungleich NULL aufgelöst wird | MyColumn1 IS NOT NULL |
Beliebig | < | Prüft, ob der linke Wert oder Ausdruck kleiner ist als der rechte Wert oder Ausdruck | MyColumn1 < MyColumn2 |
Beliebig | <= | Prüft, ob der linke Wert oder Ausdruck kleiner oder gleich dem rechten Wert oder Ausdruck ist | MyColumn1 <= MyColumn2 |
Beliebig | > | Prüft, ob der linke Wert oder Ausdruck größer ist als der rechte Wert oder Ausdruck | MyColumn1 > MyColumn2 |
Beliebig | >= | Prüft, ob der linke Wert oder Ausdruck größer oder gleich dem rechten Wert oder Ausdruck ist | MyColumn1 >= MyColumn2 |
Beliebig | IN | Prüft, ob ein Wert oder Ausdruck in einer Menge enthalten ist | MyColumn1 IN (1, 2, 3) |
Beliebig | NOT IN | Prüft, ob ein Wert oder Ausdruck nicht in einer Menge enthalten ist | MyColumn1 NOT IN (1, 2, 3) |
Text | LIKE | Prüft, ob ein Textwert oder Ausdruck mit einem Muster übereinstimmt. Das Zeichen % dient als Platzhalter. | MyColumn1 LIKE '%Apple' |
Text | NOT LIKE | Prüft, ob ein Textwert oder Ausdruck nicht mit einem Muster übereinstimmt. Das Zeichen % dient als Platzhalter. | MyColumn1 NOT LIKE '%Apple' |
Vergleiche | AND | Evaluiert zwei Vergleiche und gibt nur dann „true“ zurück, wenn beide Ausdrücke wahr sind | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Vergleiche | OR | Evaluiert zwei Vergleiche und gibt „true“ zurück, wenn einer der Ausdrücke wahr ist | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Skalarfunktionen
Skalarfunktionen verwenden Eingabewerte und geben einen einzelnen Wert zurück.
Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|
Bit-Funktionen
| ||
CAST(expr AS BIT) | Konvertiert einen Text-/Float-/Integer-Wert in einen Bit-Wert (0 oder 1) | CAST('1' AS BIT) => 1 |
Integer-Funktionen
| ||
CAST(expr AS INTEGER) | Konvertiert einen Text-/Float-/Bit-Wert in einen Integer-Wert | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Ruft die Anzahl der Tage ([datepart], DAY) von [datetime_expr1] bis [datetime_expr2] ab | 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]) | Ruft die Anzahl der Tage ([datepart], DAY) von [date_expr1] bis [date_expr2] ab | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Ruft [datepart] (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) aus [datetime_expr] ab | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Ruft die Länge von [text_expr] ab | LENGTH('Hello') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Ruft den ersten Index von [find_text_expr] in [search_text_expr] ab. Das erste Zeichen ist 1. | POSITION('at' IN 'hat') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Ruft den ersten Index von [find_text_expr] in [search_text_expr] nach dem [start]-Index ab (ein [start] von 1 bedeutet, dass der letzte Wert gesucht wird). Das erste Zeichen ist 1. | POSITION('a' IN 'a hat' FROM 1) => 4 |
Float-Funktionen
| ||
CAST(expr AS FLOAT) | Konvertiert einen Text-/Integer-/Bit-Wert in einen Float-Wert | CAST('1.01' AS FLOAT) => 1.01 |
Text-Funktionen
| ||
CAST(expr AS NVARCHAR) | Konvertiert einen Float-/Integer-/Bit-Wert in einen Text-Wert | CAST(1.01 AS NVARCHAR) => '1.01' |
TRIM([text_expr]) | Entfernt führende und nachgestellte Leerzeichen aus [text_expr] | TRIM(' xxx ') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extrahiert einen Teil von [text_expr] ab Position [start_int_expr]. Das erste Zeichen ist an Position 1. | SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Extrahiert [len_int_expr] Zeichen aus [text_expr] ab Position [start_int_expr]. Das erste Zeichen ist an Position 1. | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Ersetzt alle Instanzen von [text_expr_1] in [text_expr_3] durch den Wert [text_expr_2] | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Ersetzt alle Instanzen, die mit dem regulären Ausdruck [text_expr_1] in [text_expr_3] übereinstimmen, durch [text_expr_2] | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
Um alle Leerzeichen zu entfernen oder zu ersetzen, einschließlich Unicode-Leerzeichen, verwenden Sie die folgende Syntax anstelle von [\s]: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Trennt eine Zeichenfolge durch ein bestimmtes Zeichen und wählt daraus einen durch einen Index angegebenen Wert aus. Dadurch wird das n-te Vorkommen eines Musters extrahiert und zurückgegeben. Das erste Element beginnt bei 1. Wenn der Index außerhalb des gültigen Bereichs liegt, gibt der Ausdruck eine leere Zeichenfolge zurück. | 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]) | Stellt sicher, dass der Wert mit dem Planning-Feld „Kontocode“ kompatibel ist. Entfernt alle Leerzeichen, ersetzt dann alle nicht alphanumerischen Zeichen durch Unterstriche und schneidet Werte ab, die länger als 2048 Zeichen sind. | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
DateTime-Funktionen
| ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Konvertiert einen Text-Wert mit bekannter Struktur/bekanntem Format in einen DateTime-Wert. Nur bestimmte [timestamp_format]-Werte sind zulässig (siehe unten). | 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]) | Kürzt den DateTime-Wert [datetime_expr] auf einen gregorianischen Kalenderwert [datetime_part] von YEAR/MONTH/DAY/HOUR | TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
Date-Funktionen
| ||
CAST([text_expr] AS DATE FROM '[date_format]') | Konvertiert einen Textwert mit bekannter Struktur/bekanntem Format in einen Date-Wert. Nur bestimmte [date_format]-Werte sind zulässig (siehe unten). | 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]) | Kürzt den Date-Wert [date_expr] auf einen gregorianischen Kalenderwert [date_part] von YEAR/MONTH/DAY/HOUR | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Zeitkonstanten
Integration unterstützt zwei Konstanten in Bezug auf das aktuelle Datum und die aktuelle Uhrzeit, die in SQL-Ausdrücken verwendet werden können.
Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|
CURRENT_TIMESTAMP | Gibt das aktuelle Datum und die aktuelle Uhrzeit an und kann überall verwendet werden, wo DateTime-Objekte verwendet werden | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Gibt das aktuelle Datum an und kann überall verwendet werden, wo Date-Objekte verwendet werden | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
CASE-Anweisungen
Case-Anweisungen werden verwendet, um einen Wert basierend auf anderen Werten auszuwählen, ähnlich wie bei if-Anweisungen in vielen Sprachen.
Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | Der Wert [result_expr] des ersten [logic_expr], der „true“ zurückgibt, wird zurückgegeben. | 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 | Der Wert [result_expr] des ersten [expr#], der gleich [expr] ist, wird zurückgegeben. | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
COALESCE-Anweisungen
Coalesce evaluiert Argumente in der angegebenen Reihenfolge und gibt den ersten Wert ungleich null aus einer definierten Argumentliste zurück. Coalesce kann in einer SQL-Spalte, in SQL-Filtern in Loadern und in Join-Ausdrücken verwendet werden. Coalesce kann nicht im Importfilter in einer Staging-Tabelle verwendet werden.
Syntax | Beschreibung | Verwendungsbeispiel |
|---|---|---|
COALESCE ([expr]) | Gibt den ersten Wert ungleich null in [expr] zurück | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Ausdrücke für Tabellenbeziehungen
Wenn Sie Elemente der Tabellenbeziehung verwenden, um Tabellen zu verknüpfen, müssen Sie einen Join-Ausdruck angeben.
Diese Tabellen, die Sie verknüpfen möchten, können Spalten mit demselben Namen enthalten. Wenn Spalten mit demselben Namen vorhanden sind, müssen Sie die Spalten folgendermaßen unterscheiden:
- P für Spalten aus der primären Tabelle. Beispiel: P."MyColumn"
- R für Spalten aus anderen Tabellen. Beispiel: R."MyColumn"
Sie können die folgenden [timestamp_format]-Werte in der Funktion CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') verwenden:
- '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'
- 'ddmmyyyy'
- 'tt.mm.jj
- ‘yy.mm.dd’
- 'tt.mm.jj'
- ‘dd.mm.yy’
- 'tt-mm-jj'
- 'tt Mo jj'
- 'dd yy'
- 'dd.mm.yy'
- 'yy/mm/dd'
- 'yymmdd'
SQL-Einschränkungen und -Verwendungshinweise für datenquellenspezifische Datenimportfilter
Im Folgenden sind SQL-Einschränkungen und -Verwendungshinweise für datenquellenspezifische Datenimportfilter aufgeführt.
- NetSuite-Datenquellentabellen
- Bei der direkten Abfrage von NetSuite (im Gegensatz zur Abfrage von Datensätzen, die aus NetSuite in das Staging importiert wurden) sind Filterausdrücke auf die Funktionen beschränkt, die von NetSuite über Webservices bereitgestellt werden.
- Bei der Abfrage von NetSuite können einfache Spaltenfilter mit Vergleichsausdrücken und logischen Ausdrücken verwendet werden.
- Filter können mit UND verknüpft werden, aber nicht mit ODER.
- Operatoren (z. B. +, , /, *, $, ||) können nicht verwendet werden.
- Skalarfunktionen können nicht verwendet werden.
- Case-Anweisungen können nicht verwendet werden.
- Um eine benutzerdefinierte Spalte zu filtern, muss die benutzerdefinierte Spalte für den Import markiert werden.
- Für einige Spaltenfilter müssen bestimmte NetSuite-Features aktiviert sein, damit der Filter funktioniert.
- Einige Tabellen und einige Spalten unterstützen keine Filter.
- Tabellen-Datenquellentabellen
- Bei der direkten Abfrage einer Tabellendatei (im Gegensatz zur Abfrage von Datensätzen, die aus einer Tabelle in das Staging importiert wurden), kann der Filterausdruck nur die „Upload ID“ der abzufragenden Datei angeben. Wenn keine „Upload ID“ angegeben ist, werden Daten aus der zuletzt importierten Datei angezeigt.
- JDBC-Datenquellentabellen
- Bei der Abfrage von JDBC-Datenquellen können einfache Spaltenfilter mit Vergleichsausdrücken und logischen Ausdrücken verwendet werden.
- Operatoren (z. B. +, , /, *, $, ||) können nicht verwendet werden.
- Skalarfunktionen können nicht verwendet werden.
- Case-Anweisungen können nicht verwendet werden.
- Salesforce-Datenquellentabellen
- Bei der Abfrage von Salesforce können einfache Spaltenfilter mit Vergleichsausdrücken und logischen Ausdrücken verwendet werden.
- Operatoren (z. B. +, , /, *, $, ||) können nicht verwendet werden.
- Skalarfunktionen können nicht verwendet werden.
- Case-Anweisungen können nicht verwendet werden.
- Intacct-Datenquellentabellen
- Bei der Abfrage von Intacct können einfache Spaltenfilter mit Vergleichsausdrücken und logischen Ausdrücken verwendet werden. Dazu gehören die Anweisungen IN(..), IS NULL, IS NOT NULL, LIKE und NOT LIKE.
- Intacct unterstützt den Operator <> nicht. Verwenden Sie stattdessen den Vergleich NOT IN().
- Operatoren (z. B. +, , /, *, $, ||) können nicht verwendet werden.
- Skalarfunktionen können nicht verwendet werden.
- Case-Anweisungen können nicht verwendet werden.
- In Filtern für boolesche Spalten müssen die Schlüsselwörter „true“/„false“ verwendet werden, da Intacct 1/0 nicht als identisch mit „true“/„false“ erkennt.
- Microsoft Dynamics GP-Datenquellentabellen
- Bei der Abfrage von Microsoft Dynamics GP können einfache Spaltenfilter mit Vergleichsausdrücken und logischen Ausdrücken verwendet werden.
- Operatoren (z. B. +, , /, *, $, ||) können nicht verwendet werden.
- Skalarfunktionen können nicht verwendet werden.
- Case-Anweisungen können nicht verwendet werden.