Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

SQL Expression Reference

SQL expressions may be used for a variety of data integration purposes. The SQL language features supported vary according to the 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 follow the links below:

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
TIMESTAMP '20130102
03:04:05.006'
Date DATE '*****' Keyword DATE followed by a single quoted, constant length, representation of the date
formatted yyyy-mm-dd
DATE '20130102'
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
Numbers % Calculates of the modulus (remainder) when the left hand side is divided by the right hand side 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 training 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) => 'bbbccc'
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) =>
'bbb'
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('20130102' 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

Adaptive 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'

Table Relationship Expressions

When joining tables using 'Table Relationship' items a Join Expression must be specified. It is possible that the two tables being joined will have columns with the same name. In such cases columns from the primary table can be qualified using 'P.' (e.g. P."MyColumn"), and the related table using 'R.' (e.g. R."MyColumn")

The following table details [timestamp_format] values that can be used in the CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') function

Format
'mon dd yyyy hh:mitt'
'mm/dd/yyyy'
'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'

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.
  • Was this article helpful?