AnalytiX-BI SQL Language

The AnalytiX-BI Server supports a subset of SQL for running queries against data models. Queries can be provided as part of a point name (see the "Ad Hoc Queries" section of AnalytiX-BI Runtime) or used in a Data view.

In the next sections, we will detail the different SQL keywords that the AnalytiX-BI server’s query engine supports, and how they may differ from standard SQL.

Data Types

In AnalytiX-BI, each column, expression and parameter has an associated data type, which specifies what kind of data the attribute can hold. When two expressions that have different data types are combined by an operator or a function, the characteristics of the result are determined by the data type precedence rules.

The following table describes all the data types supported by AnalytiX-BI, in increasing order of precedence (i.e.: the first type in the list has the lowest precedence, while the last type in the list has the highest precedence).

String	Unicode character string, with no maximum length.
Guid	A 128-bit (16 bytes) unique identifier
Boolean	A Boolean (true or false) value
Byte	An 8-bit unsigned integer. Range is from 0 through positive 255
SByte	An 8-bit signed integer. Range is from negative 128 through positive 127
Int16	A 16-bit signed integer. Range is from negative 32,768 through positive 32,767
UInt16	A 16-bit unsigned integer. Range is from 0 through 65,535
Int32	A 32-bit signed integer. Range is from negative 2,147,483,648 through positive 2,147,483,647
UInt32	A 32-bit unsigned integer. Range is from 0 through 4,294,967,295
Int64	A 64-bit signed integer. Range is from negative 9,223,372,036,854,775,808 through positive 9,223,372,036,854,775,807
UnInt64	A 64-bit unsigned integer. Range is from 0 to 18,446,744,073,709,551,615
Single	A single-precision 32-bit number with values ranging from negative 3.402823e38 to positive 3.402823e38, as well as positive or negative zero, PositiveInfinity, NegativeInfinity, and not a number (NaN)
Double	A double-precision 64-bit number with values ranging from negative 1.79769313486232e308 to positive 1.79769313486232e308, as well as positive or negative zero, PositiveInfinity, NegativeInfinity, and not a number (NaN)
Decimal	A 12 bytes decimal numbers ranging from positive 79,228,162,514,264,337,593,543,950,335 to negative 79,228,162,514,264,337,593,543,950,335
TimeSpan	A time interval (duration of time or elapsed time) that is measured as a positive or negative number of days, hours, minutes, seconds, and fractions of a second
DateTime	Represents dates and times with values ranging from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.
DateTimeOffset	Represents a point in time, typically expressed as a date and time of day, relative to Coordinated Universal Time (UTC)

When an operator combines expressions of different data types, the type with the lower precedence is first converted to the data type with the higher precedence. For operators combining operand expressions having the same data type, the result of the operation has that data type.

Implicit conversion is supported from any type to a higher precedence type, with the exception of GUID which cannot be implicitly converted to other types. For more information on type conversions, consult the CAST and CONVERT functions.

The following query uses implicit conversion to convert the literal 1 into the equivalent integer. The conversion happens because string has a lower precedence than Int32 and so it gets converted.

SELECT * FROM Products WHERE ProductID = '1'

It is recommended to try to avoid implicit conversions when possible to improve runtime performance of queries.