Referentie: SQL-expressies
U kunt SQL-expressies gebruiken voor verschillende gegevensintegratiedoeleinden. Welke SQL-taalfuncties worden ondersteund, verschilt afhankelijk van het systeem waarop de SQL-instructie wordt uitgevoerd. Bijvoorbeeld:
- Query's op gegevens die zijn geïmporteerd naar fasering kunnen gebruikmaken van alle SQL-taalfuncties.
- Query's die zijn gericht op externe systemen, zoals NetSuite, maken mogelijk gebruik van SQL-filters en ondersteunen een beperkt aantal functies.
Zie de volgende secties verderop in dit document voor specifieke beperkingen en gebruiksopmerkingen voor gegevensbrontypen:
- SQL-filters voor NetSuite
- SQL-filters voor spreadsheets
- SQL-filters voor JDBC
- SQL-filters voor Salesforce
- SQL-filters voor Intacct
- SQL-filters voor Microsoft Dynamics GP
We ondersteunen slechts een beperkte set SQL-expressies. We ondersteunen andere bewerkingen op andere manieren binnen Integraties ontwerpen. Voorbeeld: als u SQL-JOIN's wilt maken, kunt u SQL-jointabellen en -kolommen toevoegen door
Jointabel
vanuit de map Aangepaste tabel
in Gegevenscomponenten
naar het tabelgebied van een gegevensbron te slepen.Letterlijke waarden
Ingesloten constanten zoals vaste DateTime-waarden of ongewijzigde tekenreeksen/getallen in een expressie met behulp van letterlijke waarden.
Gegevenstype | Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|---|
Tekst | '*****' | Teksttekenreeks tussen enkele aanhalingstekens. Als u een enkel aanhalingsteken in de tekst wilt opnemen, moet u een escapeteken gebruiken met een tweede enkel aanhalingsteken | 'Het is warm ''s middags' |
Geheel getal | # | Precies ingevoerd zoals het moet worden weergegeven | 999 |
Zwevend | #.# | Moet altijd een punt (.) binnen de zwevende waarde bevatten, zelfs als het breukdeel 0 is | 7.7 |
DateTime | TIMESTAMP '*****' | Trefwoord TIMESTAMP gevolgd door één aanhalingsteken, constante lengte, weergave van de datum in de notatie yyyy-mm-dd hh:mm:ss.SSS
U kunt een precisie van drie cijfers opgeven voor breuken in seconden. Het maximum is 6 cijfers. | 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' |
Datum | DATE '*****' | Het trefwoord DATE gevolgd door enkel aanhalingsteken, constante lengte, weergave van de datum in de notatie yyyy-mm-dd | DATUM 14 MAART 1879 |
Boole-waarde | TRUE (of FALSE) | Trefwoord TRUE of trefwoord FALSE | FALSE |
Operatoren
Numerieke operatoren voeren wiskundige bewerkingen uit op expressies, waarden of kolommen die numeriek zijn (geheel getal, zwevend, bit). Tekstoperatoren combineren twee of meer tekstexpressies, waarden of kolommen
Dit is van toepassing op | Operator | Omschrijving | Voorbeeld van gebruik |
|---|---|---|---|
Getallen | + | Telt twee numerieke waarden bij elkaar op | MyNumericColumn + 1000 |
Getallen | - | Trekt de waarde aan de rechterzijde af van de waarde aan de linkerzijde | MyNumericColumn - 1000 |
Getallen | / | Deelt de linkerzijde door de rechterzijde | MyNumericColumn / 1000 |
Getallen | * | Vermenigvuldigt twee waarden met elkaar | MyNumericColumn * 1000 |
Tekst | || | Voegt twee tekstwaarden samen | MyTextColumn || ' een achtervoegsel' |
Vergelijkings- en logische expressies
Deze expressies worden omgezet in 1 (true) of 0 (false) en kunnen worden gebruikt in tabeljoinexpressies of als de [expr] in CASE WHEN [expr] THEN [value] END-vergelijkingen. Vergelijkings- en logische expressies kunnen op tekst-, numerieke of DateTime-kolommen werken
Dit is van toepassing op | Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|---|
Any | = | Controleert of twee waarden of expressies gelijk zijn | MyColumn1 = MyColumn2 |
Any | <> | Controleert of twee waarden of expressies niet gelijk zijn | MyColumn1 <> MyColumn2 |
Any | IS NULL | Controleert of een waarde of expressie NULL is. NULL is niet gelijk aan een lege tekenreeks | MyColumn1 IS NULL |
Any | IS NOT NULL | Hiermee wordt gecontroleerd of een waarde of expressie wordt omgezet in een niet-NULL-waarde | MyColumn1 IS NOT NULL |
Any | < | Controleert of de waarde of expressie aan de linkerzijde kleiner is dan de waarde of expressie aan de rechterzijde | MyColumn1 < MyColumn2 |
Any | <= | Controleert of de waarde of expressie aan de linkerzijde kleiner is dan of gelijk is aan de waarde of expressie aan de rechterzijde | MyColumn1 <= MyColumn2 |
Any | > | Hiermee wordt gecontroleerd of de waarde of expressie aan de linkerzijde groter is dan de waarde of expressie aan de rechterzijde | MyColumn1 > MyColumn2 |
Any | >= | Hiermee wordt gecontroleerd of de waarde of expressie aan de linkerzijde groter is dan of gelijk is aan de waarde of expressie aan de rechterzijde. | MyColumn1 >= MyColumn2 |
Any | IN | Controleert of een waarde of expressie in een set voorkomt | MyColumn1 IN (1, 2, 3) |
Any | NOT IN | Controleert of een waarde of expressie niet in een set voorkomt | MyColumn1 NOT IN (1, 2, 3) |
Tekst | LIKE | Hiermee wordt gecontroleerd of een waarde of expressie met een patroon overeenkomt. Het %-teken werkt als jokerteken | MyColumn1 LIKE '%Apple' |
Tekst | NOT LIKE | Hiermee wordt gecontroleerd of een waarde of expressie van een waarde niet met een patroon overeenkomt. Het %-teken werkt als jokerteken | MyColumn1 NOT LIKE '%Apple' |
Vergelijkingen | AND | Evalueert twee vergelijkingen en retourneert alleen true als beide expressies true zijn | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Vergelijkingen | OR | Evalueert twee vergelijkingen en retourneert true als een van beide expressies true is | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Scalaire functies
Scalaire functies nemen invoerwaarden en retourneren één waarde
Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|
Bitfuncties
| ||
CAST(expr AS BIT) | Converteert een waarde voor tekst/zwevend/geheel getal naar een waarde voor bit (0 of 1) | CAST('1' AS BIT) => 1 |
Integerfuncties
| ||
CAST(expr AS INTEGER) | Converteert een waarde voor tekst/zwevend/bit naar een waarde voor geheel getal | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Haalt het aantal [datepart]'s (DAY) op van [datetime_expr1] tot [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]) | Haalt het aantal [datepart]'s (DAY) op van [date_expr1] tot [date_expr2] | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Haalt de [datepart] (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) op uit de [datetime_expr] | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Haalt de lengte op van de [text_expr] | LENGTH('Hallo') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Haalt de eerste index op van [find_text_expr] in de [search_text_expr]. Het eerste teken is 1. | POSITION('at' IN 'hat') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Haalt de eerste index op van [find_text_expr] in de [search_text_expr] na de [start]-index (een [start] van -1 betekent: zoek de laatste). Het eerste teken is 1. | POSITION('a' IN 'a hat' FROM 1) => 4 |
Functies voor zwevend
| ||
CAST(expr AS FLOAT) | Converteert een waarde voor tekst/geheel getal/bit naar een waarde voor zwevend | CAST('1.01' AS FLOAT) => 1.01 |
Tekstfuncties
| ||
CAST(expr AS NVARCHAR) | Converteert een waarde voor zwevend/geheel getal/bit naar een tekstwaarde | CAST(1.01 AS NVARCHAR) => '1.01' |
TRIM([text_expr]) | Voorloop- en volgspaties worden verwijderd uit [text_expr] | TRIM(' xxx ') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extraheert een deel van [text_expr] vanaf positie [start_int_expr]. Het eerste teken staat op positie 1. | SUBSTRING('aaabbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Extraheert [len_int_expr] tekens uit [text_expr] vanaf positie [start_int_expr]. Het eerste teken staat op positie 1. | SUBSTRING('aaabbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Alle instances van [text_expr_1] in [text_expr_3] worden vervangen door de waarde [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Alle instances die overeenkomen met de reguliere expressie [text_expr_1] in [text_expr_3] worden vervangen door [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
Gebruik deze syntaxis in plaats van [\s] om alle spaties te verwijderen of te vervangen, inclusief unicode-spaties: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Scheid een tekenreeks met een specifiek teken en selecteer een waarde uit de reeks met index. Hiermee wordt het n-de voorval van een patroon geëxtraheerd en geretourneerd. Het eerste element begint bij 1. Als de index buiten de grenzen valt, retourneert de expressie een lege tekenreeks. | 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]) | Zorgt ervoor dat de waarde compatibel is met het veld Rekeningcode in planning. Alle spaties worden verwijderd en alle niet-alfanumerieke tekens worden vervangen door onderstrepingstekens en waarden die langer zijn dan 2048 tekens worden afgekapt | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
DateTime-functies
| ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Converteert een tekstwaarde met een bekende structuur/notatie naar een DateTime-waarde. Alleen bepaalde [timestamp_format]-waarden zijn toegestaan (zie hieronder) | 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]) | Kapt DateTime [datetime_expr] af tot een Gregoriaanse kalender [datetime_part] van YEAR/MONTH/DAY/HOUR | TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
Datumfuncties
| ||
CAST([text_expr] AS DATE FROM '[date_format]') | Converteert een tekstwaarde met een bekende structuur/notatie naar een datumwaarde. Alleen bepaalde waarden voor [date_format] zijn toegestaan (zie hieronder) | 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]) | Kapt datum [date_expr] af naar een Gregoriaanse kalender [date_part] van YEAR/MONTH/DAY/HOUR | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Tijdconstanten
Integratie ondersteunt twee constanten met betrekking tot de huidige datum en tijd die kunnen worden gebruikt in SQL-expressies:
Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|
CURRENT_TIMESTAMP | Geeft de huidige DateTime en kan worden gebruikt op alle plaatsen waar DateTime-objecten worden gebruikt | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Geeft de huidige datum en kan worden gebruikt op alle plaatsen waar datumobjecten worden gebruikt | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
CASE-instructies
Case-instructies worden gebruikt om een waarde te kiezen op basis van andere waarden, net zoals if-instructies in veel talen
Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | De [result_expr] van de eerste [logic_expr] die true retourneert, wordt geretourneerd | 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 | De [result_expr] van de eerste [expr#] die gelijk is aan [expr] wordt geretourneerd | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
COALESCE-instructies
Coalesce evalueert argumenten in volgorde en retourneert de eerste niet-null-waarde uit een gedefinieerde lijst met argumenten. Coalesce kan worden gebruikt in een SQL-kolom, SQL-filters in laders en in joinexpressies. Coalesce kan niet worden gebruikt in het importfilter in een faseringstabel.
Syntaxis | Omschrijving | Voorbeeld van gebruik |
|---|---|---|
COALESCE ([expr]) | Retourneert de eerste niet-null in [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Tabelrelatie-expressies
Wanneer u tabelrelatie-items gebruikt om tabellen samen te voegen, moet u een joinexpressie opgeven.
De tabellen die u wilt samenvoegen, kunnen kolommen met dezelfde naam bevatten. Als er kolommen met dezelfde naam bestaan, moet u de kolommen van elkaar onderscheiden met behulp van:
- P voor kolommen uit de primaire tabel. Voorbeeld: P.“Mijnkolom”
- R voor kolommen uit andere tabellen. Voorbeeld: R."Mijnkolom"
U kunt deze [timestamp_format]-waarden gebruiken in de functie 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’
- 'ddmmjjj'
- 'dd/mm/jj'
- ‘yy.mm.dd’
- 'dd/mm/yy'
- ‘dd.mm.yy’
- 'dd-mm-jj'
- 'dd ma jj'
- 'ma dd jj'
- dd-mm-jj
- 'jj/mm/dd'
- ‘yymmdd’
Gegevensbronspecifieke gegevensimportfilter SQL-beperkingen en gebruiksopmerkingen
Gegevensbronspecifieke SQL-beperkingen en gebruiksopmerkingen voor gegevensimportfilters worden hieronder beschreven.
- NetSuite-gegevensbrontabellen
- Bij rechtstreekse query's op NetSuite (in plaats van query's op records die vanuit NetSuite in fasering zijn geïmporteerd), zijn filterexpressies beperkt tot de mogelijkheden die NetSuite via webservices biedt.
- Bij het uitvoeren van query's op NetSuite kunnen eenvoudige kolomfilters met vergelijkings- en logische expressies worden gebruikt.
- Filters kunnen samen worden ge-AND, maar niet samen worden ge-ORd.
- Operatoren (bijv. +, , /, *, $, ||) kunnen niet worden gebruikt.
- Scalaire functies kunnen niet worden gebruikt.
- Case-instructies kunnen niet worden gebruikt.
- Als u wilt filteren op een aangepaste kolom, moet de aangepaste kolom zijn gemarkeerd voor importeren.
- Voor sommige kolomfilters moeten specifieke NetSuite-functies zijn ingeschakeld om het filter te laten werken.
- Sommige tabellen en kolommen bieden geen ondersteuning voor filteren.
- Spreadsheetgegevensbrontabellen
- Wanneer u rechtstreeks query's uitvoert op een spreadsheetbestand (in plaats van query's op records die vanuit een spreadsheet in de fasering zijn geïmporteerd), kan de filterexpressie alleen de upload-ID opgeven van het bestand dat moet worden opgevraagd. Als er geen upload-ID is opgegeven, worden de gegevens uit het meest recent geïmporteerde bestand weergegeven.
- JDBC-gegevensbrontabellen
- Eenvoudige kolomfilters met vergelijkings- en logische expressies kunnen worden gebruikt bij het uitvoeren van query's op JDBC-gegevensbronnen.
- Operatoren (bijv. +, , /, *, $, ||) kunnen niet worden gebruikt.
- Scalaire functies kunnen niet worden gebruikt.
- Case-instructies kunnen niet worden gebruikt.
- Salesforce-gegevensbrontabellen
- Eenvoudige kolomfilters met vergelijkings- en logische expressies kunnen worden gebruikt bij het uitvoeren van query's op Salesforce.
- Operatoren (bijv. +, , /, *, $, ||) kunnen niet worden gebruikt.
- Scalaire functies kunnen niet worden gebruikt.
- Case-instructies kunnen niet worden gebruikt.
- Intacct-gegevensbrontabellen
- Bij het uitvoeren van query's op Intacct kunnen eenvoudige kolomfilters met vergelijkings- en logische expressies worden gebruikt. Dit omvat de instructies IN(..), IS NULL, IS NOT NULL, LIKE en NOT LIKE.
- Intacct biedt geen ondersteuning voor operator <>. Gebruik in plaats daarvan de vergelijking NOT IN().
- Operatoren (bijv. +, , /, *, $, ||) kunnen niet worden gebruikt.
- Scalaire functies kunnen niet worden gebruikt.
- Case-instructies kunnen niet worden gebruikt.
- Filters voor kolommen met Boole-waardes moeten de trefwoorden true/false gebruiken, aangezien Intacct 1/0 niet herkent als gelijk aan true/false.
- Microsoft Dynamics GP-gegevensbrontabellen
- Eenvoudige kolomfilters met vergelijkings- en logische expressies kunnen worden gebruikt bij het uitvoeren van query's op Microsoft Dynamics GP.
- Operatoren (bijv. +, , /, *, $, ||) kunnen niet worden gebruikt.
- Scalaire functies kunnen niet worden gebruikt.
- Case-instructies kunnen niet worden gebruikt.