Reference: SQL Expressions
You can use SQL expressions for a variety of data integration purposes. The SQL language features supported vary according to the system against which the SQL statement will be executed. For example:
- Queries against data that has been imported to staging may make use of all SQL language features.
- Queries directed to external systems, such as NetSuite, may make use of SQL filters and support a reduced set of features.
For data source type specific limitations and usage notes see these sections later in this document:
- NetSuite SQL filters
- Spreadsheet SQL filters
- JDBC SQL filters
- Salesforce SQL filters
- Intacct SQL filters
- Microsoft Dynamics GP SQL filters
We only support a limited set of SQL expressions. We support other operations in other ways within Design Integrations. Example: To create SQL JOINs, you can add SQL join tables and columns by dragging
Join Table
from the Custom Table
folder in Data Components
into the table area of a data source.Literal Values
Embedded constants such as fixed DateTime values or unchanging strings/numbers in an expression using literal values.
Data Type | Syntax | Description | Example Usage |
|---|---|---|---|
Text | '*****' | Text string surrounded by single quotes. To include a single quote within the text it must be escaped using a second single quote | 'It''s hot outside' |
Integer | # | Entered exactly as it should appear | 999 |
Float | #.# | Should always contain a period (.) within the float value, even if the fractional part is 0 | 7.7 |
DateTime | TIMESTAMP '*****' | Keyword TIMESTAMP followed by a single quoted, constant length, representation of the date formatted yyyy-mm-dd hh:mm:ss.SSS
You can specify 3 digits of precision for fractional seconds. The maximum is 6 digits. | 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 '*****' | Keyword DATE followed by a single quoted, constant length, representation of the date formatted yyyy-mm-dd | DATE '1879-03-14' |
Boolean | TRUE (or FALSE) | Keyword TRUE or keyword FALSE | FALSE |
Operators
Numeric operators perform mathematical operations on expressions, values or columns that are numeric (Integer, Float, Bit). Text operators combine two or more Text expressions, values or columns.
Applies to | Operator | Description | Example Uage |
|---|---|---|---|
Numbers | + | Adds two numeric values together | MyNumericColumn + 1000 |
Numbers | - | Subtracts the right hand value from the left hand side | MyNumericColumn 1000 |
Numbers | / | Divides the left hand side by the right hand side | MyNumericColumn / 1000 |
Numbers | * | Multiplies two values together | MyNumericColumn * 1000 |
Text | || | Concatenates two text values together | MyTextColumn || ' a suffix' |
Comparison and Logic Expressions
These expressions resolve to 1 (true) or 0 (false) and can be used in table join expressions or as the [expr] in CASE WHEN [expr] THEN [value] END comparisons. Comparison and Logic expressions can operate on Text, Numeric or DateTime columns
Applies To | Syntax | Description | Example Usage |
|---|---|---|---|
Any | = | Checks if two values or expressions are equal | MyColumn1 = MyColumn2 |
Any | <> | Checks if two values or expressions are not equal | MyColumn1 <> MyColumn2 |
Any | IS NULL | Checks if a value or expression is NULL. NULL is not equivalent to an empty string | MyColumn1 IS NULL |
Any | IS NOT NULL | Checks if a value or expression resolves to a nonNULL value | MyColumn1 IS NOT NULL |
Any | < | Checks if the left value or expression is less than the right value or expression | MyColumn1 < MyColumn2 |
Any | <= | Checks if the left value or expression is less than or equal to the right value or expression | MyColumn1 <= MyColumn2 |
Any | > | Checks if the left value or expression is greater than the right value or expression | MyColumn1 > MyColumn2 |
Any | >= | Checks if the left value or expression is greater than or equal to the right value or expression | MyColumn1 >= MyColumn2 |
Any | IN | Checks if a value or expression is contained within a set | MyColumn1 IN (1, 2, 3) |
Any | NOT IN | Checks if a value or expression is not contained within a set | MyColumn1 NOT IN (1, 2, 3) |
Text | LIKE | Checks if a text value or expression matches a pattern. The % character operates as a wildcard | MyColumn1 LIKE '%Apple' |
Text | NOT LIKE | Checks if a text value or expression does not match a pattern. The % character operates as a wildcard | MyColumn1 NOT LIKE '%Apple' |
Comparisons | AND | Evaluates two comparisons and will return true only if both expressions are true | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
Comparisons | OR | Evaluates two comparisons and will return true if either expression is true | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
Scalar Functions
Scalar functions take input values and return a single value
Syntax | Description | Example Usage |
|---|---|---|
Bit Functions | ||
CAST(expr AS BIT) | Converts a Text/Float/Integer value to a Bit (0 or 1) value | CAST('1' AS BIT) => 1 |
Integer Functions | ||
CAST(expr AS INTEGER) | Converts a Text/Float/Bit value to an Integer value | CAST('2' AS INTEGER) => 2 |
TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | Retrieves the number of [datepart]'s (DAY) from [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]) | Retrieves the number of [datepart]'s (DAY) from [date_expr1] to [date_expr2] | DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
EXTRACT([datepart] FROM [datetime_expr]) | Retrieves the [datepart] (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND) from the [datetime_expr] | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
LENGTH([text_expr]) | Retrieves the length of the [text_expr] | LENGTH('Hello') => 5 |
POSITION([find_text_expr] IN [search_text_expr]) | Retrieves the first index of [find_text_expr] in the [search_text_expr]. The first character is 1. | POSITION('at' IN 'hat') => 2 |
POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | Retrieves the first index of [find_text_expr] in the [search_text_expr] after the [start] index (a [start] of -1 means find the last). The first character is 1. | POSITION('a' IN 'a hat' FROM 1) => 4 |
Float Functions | ||
CAST(expr AS FLOAT) | Converts a Text/Integer/Bit value to an Float value | CAST('1.01' AS FLOAT) => 1.01 |
Text Functions | ||
CAST(expr AS NVARCHAR) | Converts a Float/Integer/Bit value to a Text value | CAST(1.01 AS NVARCHAR) => '1.01' |
TRIM([text_expr]) | Removes leading and trailing spaces from [text_expr] | TRIM(' xxx ') => 'xxx' |
SUBSTRING([text_expr] FROM [start_int_expr]) | Extracts a portion of [text_expr] from position [start_int_expr]. The first character is at position 1 | SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) | Extracts [len_int_expr] characters from [text_expr] from position [start_int_expr]. The first character is at position 1 | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Replaces all instances of [text_expr_1] in [text_expr_3] with the value [text_expr_2]. | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) | Replaces all instances that match the regular expression [text_expr_1] in [text_expr_3] with [text_expr_2]. | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac'
To remove or replace all whitespaces, including unicode whitespaces, use this syntax instead of [\s]: [\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000] |
SPLIT_PART([string],[delimiter],[part]) | Delimit a string by a specific character and select a value from that set by index. This extracts the nth occurrence of a pattern and returns it. The first element starts at 1. If the index is out of bounds, the expression returns an empty string. | 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]) | Ensures value is compatible with the planning Account Code field. Removes all spaces and then replaces any non-alphanumeric characters with underscores and truncates values over 2048 characters long | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
DateTime Functions | ||
CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') | Converts a Text value with a known structure/format to a DateTime value. Only certain [timestamp_format] values are allowed (see below) | 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]) | Truncates DateTime [datetime_expr] to a Gregorian calendar [datetime_part] of 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 Functions | ||
CAST([text_expr] AS DATE FROM '[date_format]') | Converts a Text value with a known structure/format to a Date value. Only certain [date_format] values are allowed (see below) | 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]) | Truncates Date [date_expr] to a gregorian calendar [date_part] of YEAR/MONTH/DAY/HOUR | TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
Time Constants
Integration supports two constants related to the current date and time that can be used in SQL expressions:
Syntax | Description | Example Usage |
|---|---|---|
CURRENT_TIMESTAMP | Gives current DateTime and can be used in all places where DateTime objects are used | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
CURRENT_DATE | Gives current Date and can be used in all places where Date objects are used | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
CASE Statements
Case statements are used to choose a value based on other values, much like if statements in many languages
Syntax | Description | Example Usage |
|---|---|---|
CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | The [result_expr] from the first [logic_expr] that returns true is returned | 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 | The [result_expr] from the first [expr#] that equals [expr] is returned | CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
COALESCE Statements
Coalesce evaluates arguments in order and returns the first non-null value from a defined argument list. Coalesce can be used in an SQL column, SQL filters in loaders, and in join expressions. Coalesce cannot be used in the import filter in a staging table.
Syntax | Description | Example Usage |
|---|---|---|
COALESCE ([expr]) | Returns the first non-null in [expr] | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
Table Relationship Expressions
When you use Table Relationship items to join tables, you must specify a Join Expression.
These tables that you want to join might contain columns with the same name. If columns with the same name exist, you must differentiate the columns from each other by using:
- P for columns from the primary table. Example: P.“MyColumn”
- R for columns from any other tables. Example: R.“MyColumn”
You can use these [timestamp_format] values in the CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') function:
- '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’
- ‘mm/dd/yy’
- ‘yy.mm.dd’
- ‘dd/mm/yy’
- ‘dd.mm.yy’
- ‘dd-mm-yy’
- ‘dd mon yy’
- ‘mon dd yy’
- ‘mm-dd-yy’
- ‘yy/mm/dd’
- ‘yymmdd’
Data Source Specific Data Import Filter SQL Limitations and Usage Notes
Data source specific Data Import Filter SQL limitations and usage notes are detailed below.
- NetSuite Data Source Tables
- When querying NetSuite directly (as opposed to querying records imported into staging from NetSuite) filter expressions are limited to the capabilities exposed by NetSuite via Web Services.
- Simple column filters with Comparison and Logic Expressions may be used when querying NetSuite.
- Filters can be ANDed together but cannot be ORed together.
- Operators (e.g. +, , /, *, $, ||) can not be used.
- Scalar Functions can not be used.
- Case Statements can not be used.
- In order to filter on a custom column, the custom column must be marked for import.
- Some column filters require specific NetSuite features to be enabled in order for the filter to work.
- Some tables and some columns do not support filtering.
- Spreadsheet Data Source Tables
- When querying a spreadsheet file directly (as opposed to querying records imported into staging from a spreadsheet) the filter expression can only specify the "Upload Id" of the file to be queried. If an "Upload Id" is not specified then data from the most recently imported file is displayed.
- JDBC Data Source Tables
- Simple column filters with Comparison and Logic Expressions may be used when querying JDBC data sources.
- Operators (e.g. +, , /, *, $, ||) can not be used.
- Scalar Functions can not be used.
- Case Statements can not be used.
- Salesforce Data Source Tables
- Simple column filters with Comparison and Logic Expressions may be used when querying Salesforce.
- Operators (e.g. +, , /, *, $, ||) can not be used.
- Scalar Functions can not be used.
- Case Statements can not be used.
- Intacct Data Source Tables
- Simple column filters with Comparison and Logic Expressions may be used when querying Intacct. This includes IN(..), IS NULL, IS NOT NULL, LIKE and NOT LIKE statements.
- Intacct does not support operator <>, instead use NOT IN() comparison.
- Operators (e.g. +, , /, *, $, ||) can not be used.
- Scalar Functions can not be used.
- Case Statements can not be used.
- Filters against boolean columns must use the true/false keywords as Intacct does not recognise 1/0 as being the same as true/false.
- Microsoft Dynamics GP Data Source Tables
- Simple column filters with Comparison and Logic Expressions may be used when querying Microsoft Dynamics GP.
- Operators (e.g. +, , /, *, $, ||) can not be used.
- Scalar Functions can not be used.
- Case Statements can not be used.