DI-ODBC SQL Grammar Detailed Reference

The SELECT statement determines which columns are returned from a SQL statement. Columns in a SELECT statement can be referenced directly, as an alias, or using a DI expression.

For example:

A SELECT statement with direct column references: SELECT AccountID, FirstName, LastName FROM data

A SELECT statement with aliased column references: SELECT PID AS "Personal ID", "First Name" + "Last Name" AS "Full Name" FROM data

A SELECT statement that selects all columns: SELECT * FROM data

A SELECT statement that evaluates a DI expression as a column: SELECT EVAL('top(dimcount(value("Account Number")))') as "Total Admissions", FirstName, LastName FROM data

The FROM statement determines which table, or tables, are queried in a SQL statement. In most situations, the table name is data, but when querying from a cPlan, each cPlan input can be referenced using the syntax of input_name.data.

For example:

SELECT * FROM data returns all columns and all rows from the cBase or cPlan.

SELECT * FROM patients.data returns all columns and all rows from the patients input in a cPlan.

 

See also Source Type Details for the Driver

The WHERE statement defines a series of filters for columns in tables referenced in a FROM statement.

For example:

SELECT * FROM data WHERE AccountID = 123456

SELECT * FROM data WHERE DaysAdmitted > 3

SELECT pt.AccountID, dt.AccountID FROM data WHERE EVAL('rolling(12, value("Date"), date("2018/05/28"))')

As shown in the last example, note that an EVAL() statement can be used. If the calculation inside the EVAL() statement returns a Boolean result, you do not need to do anything more to it in the WHERE statement. Otherwise, you must use some sort of operator to determine if the result of the EVAL() statement is true or false.

The GROUP BY statement merges duplicate values in any columns defined in the statement. A column must be referenced in the GROUP BY statement if it is referenced in the SELECT statement unless it is being used in an aggregate expression, such as COUNT().

For example:

AccountID Name Admitted
12345 John Doe True
12345 John Doe False
67890 John Doe True

SELECT COUNT(AccountID) AS "Admitted Count", Admitted FROM data GROUP BY Admitted

This statement returns two rows:

Admitted Count Admitted
2 True
1 False

NOTE: Columns can be referenced in a GROUP BY statement either by their column name or, as of DI-ODBC 7.1(25), by an alias, if you are using one.

The HAVING statement is a corollary to the GROUP BY statement. Similar to the relationship between FROM and WHERE, HAVING filters columns provided to a GROUP BY statement. A HAVING statement requires an aggregate function to work.

For example:

SELECT COUNT(AccountID) AS "Account Count", DaysAdmitted FROM data GROUP BY DaysAdmitted HAVING "Account Count" = 1

The above example returns any records where there are no duplicate Account IDs.

DI-ODBC 7.1(24) and later accepts both direct column references and aggregate functions in the HAVING clause. In an aggregate function (for example, HAVING SUM(Value) > 0), identifiers inside the function call must refer to columns in the source table; they cannot refer to column aliases.

For example:

SELECT Color, SUM(Value) FROM data GROUP BY Color HAVING SUM(Value) > 0

Direct column references and aggregate functions can be used together in the HAVING expression:

SELECT Color, SUM(Value) AS SumValue FROM data GROUP BY Color HAVING SUM(Weight) * 5 > SumValue

The ORDER BY statement sorts the results of a DI-ODBC query by defined columns. You can provide columns to sort by and whether the columns are sorted in ascending or descending order.

For example:

SELECT AccountID, LastName, FirstName FROM data ORDER BY LastName, FirstName asc

The above example orders results in ascending order by last name and then first name.

SELECT AccountID, LastName, FirstName FROM data ORDER BY LastName desc, FirstName asc

The above example orders results in descending order by last name and ascending order by first name.

The LIMIT statement is where you can set a maximum limit of rows to be returned by a DI-ODBC query. LIMIT is similar to the TOP statement. The TOP statement is used in the SELECT statement and the LIMIT statement is used at the end of a query. The rows returned by a DI-ODBC query using LIMIT or TOP are effectively random unless an ORDER BY statement is used.

For example:

SELECT * FROM data ORDER BY AccountNumber LIMIT 10

The above example returns all columns in PatientTable, but only returns the first 10 rows, ordered by AccountNumber.

SELECT TOP 10 * FROM data ORDER BY AccountNumber

The above example returns the same results as the LIMIT statement.

The OFFSET statement is a corollary to the LIMIT statement. When choosing how many rows to return with the LIMIT statement, you can use the OFFSET statement to define how many rows to skip before rows start returning.

For example:

SELECT * FROM data LIMIT 5 OFFSET 3

The above example returns five rows, but it skips the first three rows, thereby returning rows four through eight.

You can use two dashes at the start of a line to turn a line into a comment. A commented line is not evaluated when the query runs.

For example:

SELECT AccountNumber,

PatientName,

--PatientID

PatientAddress

FROM data

In the above example, the PatientID column is not returned as it has been commented out.

DI-ODBC supports multiple functions and operators that can be used in SELECT and WHERE statements. All functions and operators support the inclusion of expressions, which return values that are then evaluated by the functions and operators.

DI-ODBC supports the following operators:

Operator Description
<expression> + <expression> Returns the sum of two expressions.
<expression> - <expression> Returns the difference of two expressions.
<expression> * <expression> Returns the product of two expressions.
<expression> / <expression> Returns the quotient of two expressions.
<expression> = <expression> Returns true when two expressions are equivalent.
<expression> == <expression> Returns true when two expressions are equivalent.
<expression> <> <expression> Returns true when two expressions are not equivalent.
<expression> != <expression> Returns true when two expressions are not equivalent.
<expression> < <expression> Returns true when the left-side expressions is less than the right-side expression.
<expression> <= <expression> Returns true when the left-side expressions is less than or equal to the right-side expression.
<expression> > <expression> Returns true when the left-side expressions is greater than the right-side expression.
<expression> >= <expression> Returns true when the left-side expressions is greater than or equal to the right-side expression.
<expression> AND <expression> Returns true when both expressions return true.
<expression> OR <expression> Returns true when either or both expressions return true.
<expression> || <expression> Returns true when either or both expressions return true.
- <expression> Returns the opposite result of the specified expression.
<expression> ISNULL Returns true when the expression returns a null value.
<expression> IS NULL Returns true when the expression returns a null value.
<expression> IS NOT NULL Returns true when the expression does not return a null value.
<expression> IN ( [ <in-expressions> ] ) Returns true if the result of any of the <in-expressions> matches the result of the left-side expression.
<expression> NOT IN ( [ <in-expressions> ] ) Returns true if none of the results of any of the <in-expressions> matches the result of the left-side expression.
<expression> BETWEEN <expression> AND <expression> Returns true if the result of the left-side expression can be found between the results of either of the right-side expressions.
<expression> + <interval-expression> Returns the result of an expression added to the result of an INTERVAL statement.
<expression> - <interval-expression> Returns the result of an expression subtracted by the result of an INTERVAL statement.

Compute the absolute value of a number.

ABS(expression : numeric) : numeric

For example:

ABS(10) returns 10

ABS(-10) returns 10

Compute the arc cosine (in radians) of a number.

ACOS(expression : numeric) : numeric

For example:

ACOS(0.45) returns 1.104030

ACOS(CALC('Value') / 2000) returns 0.7162459 when Value is 1508.5516

Compute the arc sine (in radians) of a number.

ASIN(expression : numeric) : numeric

For example:

ASIN(CALC('Value') / 2000) returns 0.8545504 when Value is 1508.5516

Compute the arc tangent (in radians) of a number.

ATAN(expression : numeric) : numeric

For example:

ATAN(CALC('Value') / 2000) returns 0.646232 when Value is 1508.5516

Compute the smallest integer not less than a number, or to a specified unit. In other words, CEILING() always rounds up.

CEILING(expression : numeric) : numeric

For example:

CEILING(2.3) returns 3

CEILING(2.7) returns 3

CEILING(CALC('Value')) returns -91 when the Value is -91.4484

Compute the cosine of an angle (measured in radians).

COS(expression : numeric) : numeric

For example:

COS(100) returns 0.862319

COS(45) returns 0.525322

COS(0.45) returns 0.900447

COS(CALC('Value')) returns 0.8325361 when Value is 1508.5516

Compute the cotangent of an angle (measure in radians).

COT(expression : numeric) : numeric

For example:

COT(100) returns -1.702957

COT(45) returns 0.617397

COT(0.45) returns 2.070157

COT(CALC('Value')) returns 2.705638 for a Value of 525

Convert a radian value to its equivalent value in degrees.

DEGREES(expression : numeric) : numeric

For example:

DEGREES(1.74533) returns 100.000041

DEGREES(CALC('Value')) returns 1432.394467 when Value is 25

Compute the value of e (Euler's number) raised to the power of a number.

EXP(expression : numeric) : numeric

For example:

EXP(20 / 1000) returns 1.0202013

EXP(CALC('Value') / 1000) returns 4.520179 for a Value of 1508.5516

Compute the largest integer not greater than a number.

FLOOR(expression : numeric) : numeric

For example:

FLOOR(2.3) returns 2

FLOOR(2.7) returns 2

FLOOR(-2.3) returns -3

FLOOR(CALC('Value')) returns -92 for a Value of -91.4484

Compute the natural logarithm (ln x) of a number.

LOG(expression : numeric) : numeric

For example:

LOG(1) returns 0

LOG(50) returns 3.912023

LOG(CALC('Value')) returns 4.693456 for a Value of 109.23

Compute the common (base 10) logarithm of a number.

LOG10(expression : numeric) : numeric

For example,

LOG10(100) returns 2.0

LOG10(1) returns 0.0

LOG10(100000) returns 5.0

LOG10(10) returns 1.0

LOG10(CALC('Value')) returns 3.1785602 for a Value of 1508.5516

Compute the remainder of dividing one number by another.

MOD(expression: numeric, expression : numeric) : numeric

For example:

MOD(10, 3) returns 1

MOD(20, 5) returns 0

MOD(48, 55, 12) returns 0.55

MOD(CALC('Value'), 123.4) returns 27.7516 for a Value of 1508.5516

Return the value of pi.

PI() : numeric

Compute one number raised to the power of another.

POWER(expression : numeric, expression : numeric) : numeric

For example:

POW(8, 3) returns 512

POW(3, 12) returns 531441

POW(CALC('Value'), 1.5) returns 58592.2606643 for a Value of 1508.5516

Convert a degree value to its equivalent value in radians.

RADIANS(expression : numeric) : numeric

For example:

RADIANS(100) returns 1.745329

RADIANS(CALC('Value')) returns 3.1415927 for a Value of 180

Return a random number between 0 and 1.

RAND() : numeric

Compute the sign (-1, 0, or 1) of a number.

SIGN(expression : numeric) : numeric

For example:

SIGN(-99) returns -1

SIGN(0.1) returns 1

SIGN(0) returns 0

SIGN(CALC('Value') - 1600) returns -1 for a Value of 1508.5516

Compute the sine of an angle (measured in radians).

SIN(expression : numeric) : numeric

For example:

SIN(1508.5516) returns 0.5539708

SIN(CALC('Value')) returns 0.5539708 for a Value of 1508.5516

Compute the square root of a number.

SQRT(expression : numeric) : numeric

For example:

SQRT(25) returns 5.0

SQRT(30) returns 5.4772256

SQRT(CALC('Value')) returns 38.8400772 for a Value of 1508.5516

Compute the tangent of an angle (measured in radians).

TAN(expression : numeric) : numeric

For example:

TAN(2007.396) returns -0.0818879

TAN(CALC('Value')) returns 0.6654016 for a Value of 1508.5516

Concatenate two strings into one string.

CONCAT(expression : string, expression : string) : string

For example:

CONCAT('Smith, ', 'John') returns Smith, John

CONCAT('Dr. ', CALC('Doctor Name')) returns Dr. Jane Doe for a Doctor Name of "Jane Doe"

Return the length of a string.

LENGTH(expression : string) : numeric

For example:

LENGTH('Name') returns 4

LENGTH('How Long?') returns 9

Return the lower-case variant of the specified string.

LOWER(expression : string) : string

LCASE(expression : string) : string

For example:

LOWER('PATIENT ID') returns patient id

LCASE('ACCOUNT NUMBER') returns account number

Return a string that is the substring of a string expression starting from the left at character offset start (starting at 1) and having length characters. Both the start expression and length expression must be integers. If the length expression exceeds the length of the string, the result is the substring starting at the offset start and ending at the end of the string.

SUBSTRING(string_expression : string, start_expression : numeric, length_expression : numeric) : string

For example:

SUBSTRING('this is a test', 2, 5) returns "his i"

SUBSTRING('this is a test', 3, 13) returns "is is a test"

Return the upper-case variant of the specified string.

UPPER(expression : string) : string

UCASE(expression : string) : string

For example:

UPPER('john smith') returns JOHN SMITH

UPPER('Patient ID') returns PATIENT ID

Returns a date object with the date as it is at the start of running the script.

In DI-ODBC 7.1(25) and later, you can use the DATE statement to make a literal date. The format must be "YYYY-MM-DD".

For example:

DATE "2022-12-31" AS "Date"

You can also use the driver-agnostic ODBC formatting to make a date if you are working with two different types of ODBC platforms. The format for that is `{d 'YYYY-MM-DD'}`.

For example:

{d '2022-12-31'} AS "Date"

Return the name of the day of the week as a string for the day portion of the specified date.

DAYNAME(expression : date) : string

For example:

DAYNAME(DATE '2022-10-22') returns Saturday

Return the day of the month in integer form for the specified date.

DAYOFMONTH(expression : date) : numeric

For example:

DAYOFMONTH(DATE '2022-10-15') returns 15

In DI-ODBC 7.1(25) and later, you can use the EXTRACT statement to get a date element out of a date, datetime, or period object. Only YEAR, MONTH, and DAY are supported as units.

EXTRACT(unit FROM date).

For example:

EXTRACT(YEAR FROM DATE "12-31-2022") returns 2022.

In DI-ODBC 7.1(25) and later, you can use the INTERVAL statement to offset a date value. The structure of an INTERVAL statement is `<val> + INTERVAL <num> <unit>` or `<val> - INTERVAL <num> <unit>`. Only YEAR, MONTH, and DAY are supported as units.

For example:

To offset a date forward by 3 months: "Date" + INTERVAL 3 MONTH

To offset a date backwards by 1 year: "Date" - INTERVAL 1 YEAR

You can also use an expression for the number.

For example:

To offset a date backwards by 14 days: "Date" - INTERVAL 7 * 2 DAY

Return the month number for a date or datetime value.

MONTH(expression : date/datetime) : numeric

For example:

MONTH(DATE '2022-11-24') returns 11

MONTH(TIMESTAMP '2023-12-04 12:30:10') returns 12

Return the string name of the month in the specified date value.

MONTHNAME(expression : date) : string

For example:

MONTHNAME(DATE '2021-10-24') returns October

Return the date and time as it is at the start of the script.

In DI-ODBC 7.1(25) and later, you can use the TIMESTAMP statement to make a literal date and time. The format must be "YYYY-MM-DD hh:mm:ss".

For example:

TIMESTAMP "2022-12-31 10:11:12" AS "DateTime"

You can also use the driver-agnostic ODBC formatting to make a timestamp if you are working with two different types of ODBC platforms. The format for that is `{ts 'YYYY/MM/DD hh:mm:ss'}`.

For example:

{ts '2022-12-31 10:11:12' AS "DateTime"

Return the year containing a date or period.

YEAR(expression : date/datetime) : numeric

For example:

YEAR(DATE '2021-09-02') returns 2021

YEAR(DATE '2019-10-21 12:30:02') returns 2019

Returns the first expression if it is not null, and the second expression if the first expression is null.

IFNULL(expression : expression, expression : expression) : expression

For example:

IFNULL(CALC('Value'), 'Replace') returns "Replace" for a Value of NULL

IFNULL('Not null value', 'Null value') returns "Not null value"

Return a count of the results of an expression.

COUNT(expression : expression) : numeric

COUNT(DISTINCT expression : expression) : numeric

For example:

SELECT COUNT(*) FROM data returns a total count of all records in the data table.

SELECT COUNT('Admit Date') FROM data returns a total count of all records in the Admit Date column.

SELECT COUNT(DISTINCT 'Admit Date') FROM data returns a total count of all unique records in the Admit Date column.

Return the largest numeric value in a specified expression.

MAX(expression : numeric) : numeric

For example:

SELECT MAX('Revenue') FROM data returns the largest value in the Revenue column.

Return the smallest numeric value in a specified expression.

MIN(expression : numeric) : numeric

For example:

SELECT MIN('Revenue') FROM data returns the smallest value in the Revenue column.

Return the standard deviation numeric value of a specified expression.

STDEV(expression : numeric) : numeric

For example:

SELECT STDEV('Revenue') FROM data returns the standard deviation of all values in the Revenue column.

Return an aggregated sum of all numbers in a specified expression.

SUM(expression : numeric) : numeric

For example:

SELECT SUM('Revenue') FROM data returns the sum of all values in the Revenue column.

DI functions allow you to evaluate Spectre expressions in your DI-ODBC query.

The CALC statement allows you to reference an existing calc from a cPlan. When the query runs, the referenced calc expression runs.

For example:

SELECT CALC("Total Admissions") AS "Total Admissions" FROM data

The above expression references the Total Admissions calc, which is defined in a cPlan, and returns the result of that calc as the Total Admission column.

The EVAL statement allows you to define a Spectre expression string in your query, which runs as written when the query runs.

For example:

SELECT EVAL('top(dimcount(value("Account Number")))') AS "Total Admissions" FROM data

The above expression runs the top(dimcount(value("Account Number"))) expression and returns the results as the Total Admission column.

The MEASURE() statement allows you to reference an existing measure from a Measure Factory. When the query runs, the referenced measure expression runs.

For example:

SELECT MEASURE("Total Admissions") AS "Total Admissions" FROM data

The above expression references the Total Admissions measure, which is defined in a Measure Factory, and returns the result of that measure as the Total Admission column.

The escaped functions syntax is for ODBC functions that the driver doesn't have to implement. If two different SQL servers have two different names for functions, the driver converts the escaped function into the equivalent valid function for that driver.

{fn <function> }

As of DI-ODBC 7.1(29), parameter markers are supported in ODBC queries. A question mark (?) signifies where a parameter is inserted in a query.

For example:

SELECT * FROM data WHERE AccountID = ? returns all data where the AccountID column matches a supplied value that replaces the parameter, represented by the question mark.

NOTE: To use parameters, your ODBC application must have a way to supply values to parameters. Spectre has this ability when using an odbc-input (see ODBC Input Build Tags).