• Sign In
  1. Start Page
  2. Data Basics
  3. SQL Queries

LabKey SQL Reference

_Documentation

LabKey SQL Reference

Print

LabKey SQL 

LabKey SQL is a SQL dialect that supports (1) most standard SQL functionality and (2) provides extended functionality that is unique to LabKey, including:

  • Lookup columns. Lookup columns use an intuitive syntax to access data in other tables to achieve what would normally require a JOIN statement. For example: "SomeTable.ForeignKey.FieldFromForeignTable" For details see Lookups.
  • Security. Before execution, all SQL queries are checked against the user's security role/permissions.  
  • Parameterized SQL statements: the PARAMETERS keyword lets you define parameters for a query.  An associated API gives you control over the parameterized query from JavaScript code. See Parameterized SQL Queries.
  • Pivot tables: the PIVOT...BY expression provides an intuitive syntax for creating pivot tables. See Create a Pivot Query.
  • User-related functions: USERID() and ISMEMBEROF(groupid) lets you control query visibility based on the user's group membership. 

Keywords

Keyword Description
AS Aliases can be explicitly named using the AS keyword. Note that the AS keyword is optional: the following select clauses both create an alias called "Name":

    SELECT LCASE(FirstName) AS Name
    SELECTLCASE(FirstName) Name

Implicit aliases are automatically generated for expressions in the SELECT list.  In the query below, an output column named "Expression1" is automatically created for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName)
    FROM PEOPLE
ASCENDING, ASC Return results in ascending value order.

    ORDER BY Weight ASC 
CAST(AS) CAST(R.d AS VARCHAR)

Defined valid datatype keywords which can be used as cast/convert targets, and to what java.sql.Types name each keyword maps. Keywords are case-insensitive.

    BIGINT
    BINARY
    BIT
    CHAR
    DECIMAL
    DATE
    DOUBLE
    FLOAT
    GUID
    INTEGER
    LONGVARBINARY
    LONGVARCHAR
    NUMERIC
    REAL
    SMALLINT
    TIME
    TIMESTAMP
    TINYINT
    VARBINARY
    VARCHAR

Examples:

CAST(TimeCreated AS DATE)

CAST(WEEK(i.date) as INTEGER) as WeekOfYear,
DESCENDING, DESC Return results in descending value order.
DISTINCT Return distinct, non duplicate values.

    SELECT DISTINCT Country
    FROM Demographics 
FALSE  
FROM

The FROM clause in LabKey SQL must contain at least one table. It can also contain JOINs to other tables. Commas are supported in the FROM clause:

    FROM TableA, TableB
    WHERE TableA.x = TableB.x

Nested joins are supported in the FROM clause:

    FROM TableA LEFT JOIN (TableB INNER JOIN TableC ON ...) ON...

To refer to tables in LabKey folders other than the current folder, see Cross-Folder Queries.
GROUP BY Used with aggregate functions to group the results.  Defines the "for each" or "per".  The example below returns the number of records "for each" participant:

    SELECT "Physical Exam".ParticipantId, COUNT("Physical Exam".Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY "Physical Exam".ParticipantId
HAVING Used with aggregate functions to limit the results.  The following example returns participants with 10 or more records in the Physical Exam table:

    SELECT "Physical Exam".ParticipantId, COUNT("Physical Exam".Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY "Physical Exam".ParticipantId
    HAVINGCOUNT("Physical Exam".Created) > 10
JOIN,
RIGHT JOIN,
LEFT JOIN,
FULL JOIN		 Example:

    SELECT "Physical Exam".*
    FROM "Physical Exam"
    FULL JOIN "Lab Results"
    ON "Physical Exam".ParticipantId = "Lab Results".ParticipantId 
LIMIT Limits the number or records returned by the query.  The following example returns the 10 most recent records:

    SELECT *
    FROM "Physical Exam"
    ORDER BY Created DESC LIMIT 10
ORDER BY Often used with LIMIT to improve performance:

    SELECT "Physical Exam".ParticipantID,
    "Physical Exam".Height_cm AS Height
    FROM "Physical Exam"
    ORDER BY Height DESC LIMIT 5

Troubleshooting: "Why is the ORDER BY clause being ignored?"

When authoring queries in LabKey SQL, ORDER BY clauses may appear to not be respected in the results displayed to the user. This is because a LabKey SQL query is typically processed as a subquery within a parent query, and the parent's sort order overrides the ORDER BY clause in the subquery.

Two recommended solutions:
(1) Define the sort in the parent query using the grid view customizer.
(2) Use the "config.sort" property of the API call.
PARAMETERS Queries can declare parameters using the PARAMETERS keyword. Default values data types are supported as shown below:

    PARAMETERS (X INTEGER DEFAULT 37)
    SELECT *
    FROM "Physical Exam"
    WHERE Temp_C = X

Parameter names will override any unqualified table column with the same name.  Use a table qualification to disambiguate.  In the example below, R.X refers to the column while X refers to the parameter:

    PARAMETERS(X INTEGER DEFAULT 5)
    SELECT *
    FROM Table R
    WHERE R.X = X

Supported data types for parameters are: BIGINT, BIT, CHAR, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TIMESTAMP, TINYINT, VARCHAR

Parameter values can be passed via JavaScript API calls to the query. For details see Parameterized SQL Queries.
PIVOT BY Re-visualize a table by rotating or "pivoting" a portion of it, essentially promoting cell data to column headers. See Write a Pivot Query for examples.
SELECT SELECTqueries are the only type of query that can currently be written in LabKey SQL.  Sub-selects are allowed both asanexpression,and in the FROM clause.

Aliases are automatically generated for expressions after SELECT.  In the query below, an output column named "Expression1" is automatically generated for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName) FROM...
TRUE  
UNION, UNION ALL The UNION clause is the same as standard SQL.  LabKey SQL supports UNION in subqueries. 
WHERE Filter the results for certain values. Example:

    SELECT "Physical Exam".*
    FROM "Physical Exam"
    WHERE YEAR(Date) = '2010' 

Operators

Operator Description
String Operators  
 || String concatenation. For example:    
   
    SELECT Demographics.ParticipantId,
    Demographics.City || ', ' || Demographics.State AS CityOfOrigin
    FROM Demographics
 LIKE  
 NOT LIKE  
Arithmetic Operators  
 + Add
 - Subtract
 * Multiply
 / Divide
Comparison operators  
 = Equals
 != Does not equal
 <>  Does not equal
 >  Is greater than
 <  Is less than
 >= Is greater than or equal to
 <= Is less than or equal to
 IS NULL Is NULL
 IS NOT NULL Is NOT NULL
 BETWEEN Between two values. Values can be numbers, strings or dates.
 IN In
 NOT IN Not in
Bitwise Operators  
 & Bitwise AND
 | Bitwise OR
 ^ Bitwise exclusive OR
AND Operators  
 AND Logical AND
 OR Logical OR
 LIKE Like
 NOT LIKE Not like

Operator Order of Precedence

Order of Precedence Operators
 1  - (unary) , + (unary), CASE
 2  *, / (multiplication, division) 
 3  +, -, & (binary plus, binary minus)
 4  & (bitwise and)
 5  ^ (bitwise xor)
 6  | (bitwise or)
 7  || (concatenation)
 8  <, >, <=, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE 
 9  =, IS, IS NOT, <>, !=  
10  NOT
11  AND
12  OR

 NOTE: Prior to 14.3 bitwise operator precedence varies based on the underlying SQL database.  We suggest always using parentheses when using bitwise operators.

Aggregate Functions

Function Description
COUNT The special syntax COUNT(*) is supported as of LabKey v9.2.
MIN Minimum
MAX Maximum
AVG Average
SUM Sum 
STDDEV Standard deviation
GROUP_CONCAT An aggregate function, much like MAX, MIN, AVG, COUNT, etc. It can be used wherever the standard aggregate functions can be used, and is subject to the same grouping rules. Like the built-in MySQL functionality, it will return a string value which is comma-separated list of all of the values for that grouping. A custom separator, instead of the default comma, can be specified.  The example below specifies a semi-colon as the separator:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, ';') AS CATEGORIES FROM SomeSchema.SomeTable

To use a line-break as the separator, use the following:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, chr(10)) AS CATEGORIES FROM SomeSchema.SomeTable  

SQL Functions

Many of these functions are similar to standard SQL functions -- see the JBDC escape syntax documentation for additional information.

Function Description
abs(value) Returns the absolute value.
acos(value) Returns the arc cosine.
age(date1, date2)

Supplies the difference in age between the two dates, calculated in years.
age(date1, date2, interval)

The interval indicates the unit of age measurement, either SQL_TSI_MONTH or SQL_TSI_YEAR.
age_in_months(date1, date2) Behavior is undefined if date2 is before date1.
age_in_years(date1, date2) Behavior is undefined if date2 is before date1.
asin(value) Returns the arc sine.
atan(value) Returns the arc tangent.
atan2(value1, value2) Returns the arctangent of the quotient of two values.
case LabKey SQL parser sometimes requires the use of additional parentheses within the statement.

    CASE (value) WHEN (test1) THEN (result1) ELSE (result2) END
    CASE WHEN (test1) THEN (result1) ELSE (result2) END
ceiling(value) Rounds the value up.
coalesce(value1,...,valueN) Returns the first non-null value in the argument list. Use to set default values for display.
concat(value1,value2) Concatenates two values. 
contextPath() Returns the context path starting with “/” (e.g. “/labkey”). Returns the empty string if there is no current context path. (Returns VARCHAR.)
cos(radians) Returns the cosine.
cot(radians) Returns the cotangent.
curdate() Returns the current date.
curtime() Returns the current time
dayofmonth(date) Returns the day of the month (1-31) for a given date.
dayofweek(date) Returns the day of the week (1-7) for a given date. (Sun=1 and Sat=7)
dayofyear(date) Returns the day of the year (1-365) for a given date.
degrees(radians) Returns degrees based on the given radians.
exp(n) Returns Euler's number e raised to the nth power. e = 2.71828183 
floor(value) Rounds down to the nearest integer.
folderName() LabKey SQL extension function. Returns the name of the current folder, without beginning or trailing "/". (Returns VARCHAR.)
folderPath() LabKey SQL extension function. Returns the current folder path (starts with “/”, but does not end with “/”). The root returns “/”. (Returns VARCHAR.)
greatest(a, b, c, ...) Returns the greatest value from the list expressions provided. Any number of expressions may be used. The expressions must have the same data type, which will also be the type of the result. The LEAST() function is similar, but returns the smallest value from the list of expressions. GREATEST() and LEAST() are not implemented for SAS databases.

When NULL values appear in the list of expressions, different database implementations as follows:

- Postgres & MS SQL Server ignore NULL values in the arguments, only returning NULL if all arguments are NULL.
- Oracle and MySql return NULL if any one of the arguments are NULL. Best practice: wrap any potentially nullable arguments in coalesce() or ifnull() and determine at the time of usage if NULL should be treated as high or low.

Example:

SELECT greatest(MyAssay.score_1, MyAssay.score_2, MyAssay.score_3) As HIGH_SCORE
FROM MyAssay 
hour(time) Returns the hour for a given date/time.
ifdefined(column_name) IFDEFINED(NAME) allows queries to reference columns that may not be present on a table. Without using IFDEFINED(), LabKey will raise a SQL parse error if the column cannot be resolved. Using IFDEFINED(), a column that cannot be resolved is treated as a NULL value. The IFDEFINED() syntax is useful for writing queries over PIVOT queries or assay tables where columns may be added or removed by an administrator.
ifnull(testValue, defaultValue) If testValue is null, returns the defaultValue.  Example: IFNULL(Units,0)
isequal LabKey SQL extension function. ISEQUAL(a,b) is equivalent to (a=b OR (a IS NULL AND b IS NULL))
ismemberof(groupid) LabKey SQL extension function. Returns true if the current user is a member of the specified group.
javaConstant(fieldName) LabKey SQL extension function. Provides access to public static final variable values.  For details see Using SQL Functions in Java Modules.
lcase(string) Convert all characters of a string to lower case.
least(a, b, c, ...) Returns the smallest value from the list expressions provided. For more details, see greatest() above.
left(string, integer) Returns the left side of the string, to the given number of characters. Example: SELECT LEFT('STRINGVALUE',3) returns 'STR'
length(string) Returns the length of the given string.
locate(substring, string) locate(substring, string, startIndex) Returns the location of the first occurrence of substring within string.  startIndex provides a starting position to begin the search. 
log(n) Returns the natural logarithm of n.
log10(n) Base base 10 logarithm on n.
lower(string) Convert all characters of a string to lower case.
ltrim(string) Trims white space characters from the left side of the string. For example: LTRIM('     Trim String')
minute(time) Returns the minute value for the given time. 
mod(dividend, divider) Returns the remainderofthedivisionofdividendbydivider.
moduleProperty(module name,  property name)

LabKey SQL extension function. Returns a module property, based on the module and property names. For details see Using SQL Functions in Java Modules. 
month(date) Returns the month value (1-12) of the given date. 
monthname(date) Return the month name of the given date.
now() Returns the system date and time.
overlaps LabKey SQL extension function. Supported only when Postrgres is installed as the primary database.    
   
    SELECT OVERLAPS (START1, END1, START2, END2) AS COLUMN1 FROM MYTABLE

The LabKey SQL syntax above is translated into the following Postgres syntax:    
   
    SELECT (START1, END1) OVERLAPS (START2, END2) AS COLUMN1 FROM MYTABLE
pi() Returns the value of π.
power(base, exponent) Returns base raised to the power of exponent. For example, power(10,2) returns 100.
quarter(date) Returns the yearly quarter for the given date where the 1st quarter = Jan 1-March 31, 2nd quarter = April 1-June 30, 3rd quarter = July 1-Sept30, 4th quarter = Oct 1-Dec 31
radians(degrees) Returns the radians for the given degrees.
rand(), rand(seed) Returns a random number between 0 and 1.
repeat(string, count) Returns the string repeated the given number of times. SELECT REPEAT('Hello',2) returns 'HelloHello'.
round(value, precision) Rounds the value to the specified number of decimal places.  ROUND(43.3432,2) returns 43.34
rtrim(string) Trims white space characters from the right side of the string. For example: RTRIM('Trim String     ')
second(time) Returns the second value for the given time.
sign(value) Returns the sign, positive or negative, for the given value. 
sin(value) Returnsthesineforthe given value.
startswith(string, prefix) Tests to see if the string starts with the specified prefix. For example, STARTSWITH('12345','2') returns FALSE.
sqrt(value) Returns the square root of the value.
substring(string, start, end) Returns a portion of the string as specified by the start and end locations.
tan(value)

Returns the tangent of the value.
timestampadd(interval, number_to_add, timestamp)

Adds an interval to the given timestamp value. The interval value must be surrounded by quotes. Possible values for interval: 

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

Example: TIMESTAMPADD('SQL_TSI_QUARTER', 1, "Physical Exam".date) AS NextExam
timestampdiff(interval, timestamp1, timestamp2)

The interval must be surrounded by quotes. This differs from JDBC syntax. Note that PostgreSQL does not support the following intervals:

SQL_TSI_FRAC_SECOND 
SQL_TSI_YEAR 
SQL_TSI_MONTH 
SQL_TSI_QUARTER 
SQL_TSI_WEEK 

As a workaround, use the 'age' functions defined above.

Example: TIMESTAMPDIFF('SQL_TSI_DAY', SpecimenEvent.StorageDate, SpecimenEvent.ShipDate)
truncate(numeric value, precision) Truncates the numeric value to the precision specified. This is an arithmetic truncation, not a string truncation.
  TRUNCATE(123.4567,1) returns 123.4
  TRUNCATE(123.4567,2) returns 123.45
  TRUNCATE(123.4567,-1) returns 120.0 
ucase(string), upper(string) Converts all characters to upper case.
userid() LabKey SQL extension function. Returns the userid, an integer, of the logged in user. 
username() LabKey SQL extension function. Returns the current user display name. VARCHAR
week(date) Returns the week value (1-52) of the given date.
year(date) Return the year of the given date.  Assuming the system date is March 4 2023, then YEAR(NOW()) return 2023.

PostgreSQL Specific Functions

LabKey SQL supports the following PostgreSQL functions. See the  PostgreSQL docs for usage details.

 PostgreSQL Function   Docs 
 ascii(value) Returns the ASCII code of the first character of value.   
 btrim(value,
  trimchars)		 Removes characters in trimchars from the start and end of string. trimchars defaults to white space.

BTRIM(' trim    ') returns TRIM 
BTRIM('abbatrimabtrimabba', 'ab') returns trimabtrim

 character_length(value),  char_length(value)

 Returns the number of characters in value.
 chr(integer_code) Returns the character with the given integer_code.  

CHR(70) returns F
 decode(text,
  format)		 See the PostgreSQL docs.
 encode(binary,
  format)		 See the PostgreSQL docs.
 initcap(string) Converts the first character of each separate word in string to uppercase and the rest to lowercase. 
 lpad(string, 
  int,
  fillchars)		 Pads string to length int by prepending characters fillchars. 
 md5(text) Returns the hex MD5 value of text. 
 octet_length(string)  Returns the number of bytes in string.
 overlaps See above for syntax details.
 quote_ident(string) Returns string quoted for use as an identifier in an SQL statement. 
 quote_literal(string) Returns string quoted for use as a string literal in an SQL statement.
 regexp_replace  See PostgreSQL docs for details: reference doc, example doc
 repeat(string, int) Repeats string the specified number of times.
 replace(string, 
  matchString, 
  replaceString)		 Searches string for matchString and replaces occurrences with replaceString.
 rpad(string, 
  int,
  fillchars)		 Pads string to length int by postpending characters fillchars.
 split_part(string,
  delmiter,
  int)		 Splits string on delimiter and returns fragment number int (starting the count from 1).

SPLIT_PART('mississippi', 'i', 4) returns 'pp'.
 strpos(string,
  substring)		 Returns the position of substring in string. (Count starts from 1.)
 substr(string,
 fromPosition,
 charCount)

Extracts the number of characters specified by charCount from string starting at position fromPosition.

SUBSTR('char_sequence', 5, 2) returns '_s' 
 to_ascii(string,
  encoding) 		 Convert string to ASCII from another encoding.
 to_hex(int) Converts int to its hex representation.
 translate(text,
  fromText,
  toText) 		 Characters in string matching a character in the fromString set are replaced by the corresponding character in toString.
 to_char See Data Type Formatting Functions in the PostgreSQL docs.
 to_date(textdate,
  format) 		 See Data Type Formatting Functions in the PostgreSQL docs. 
 to_timestamp See Data Type Formatting Functions in the PostgreSQL docs.
 to_number See Data Type Formatting Functions in the PostgreSQL docs.

MS SQL Server Specific Functions

LabKey SQL supports the following SQL Server functions. See the SQL Server docs for usage details.

MS SQL Server Function Description
ascii(value) Returns the ASCII code of the first character of value.  
char(int), chr(int) Returns an character for the specified ascii code int. 
charindex(string, 
 searchString,
 index) 		 Returns the position of searchString in string, starting the search at index.
difference(string,string) Returns the difference between the soundex values of two expressions as an integer. See the MS SQL docs.
isnumeric(expression) Determines whether an expression is a valid numeric type. See the MS SQL docs.
len(string) Returns the number of characters in string. Trailing white space is excluded. 
patindex(pattern,string) Returns the position of the first occurrence of pattern in string. See the MS SQL docs. 
quotename See the MS SQL docs.

replace(string,pattern, replacement)

 Replaces all occurences of pattern with replacement in the string provided. See the MS SQL docs.
replicate(string,int) Replicate string the specified number of times.
reverse(string) Returns the string in reverse character sequence.
right(string,index) Returns the right part of string to the specified index.
soundex See the MS SQL docs.
space(int) Returns a string of white space characters.
str(float,length,decimal) See the MS SQL docs. 
stuff(string,
 start,
 length,
 replaceWith)		 Inserts replaceWith into string. Deletes the specified length of characters in string at the start position and then inserts replaceWith. See the MS SQL docs.

General Syntax

Syntax Item Description
Case Sensitivity Schema names, table names, column names, SQL keywords, function names are case-insensitive in LabKey SQL.
Comments  Comments that use the standard SQL syntax can be included in queries. '--' starts a line comment. Also, '/* */' can surround a comment block:

-- line comment 1
-- line comment 2
/* block comment 1
    block comment 2 */
SELECT ... 
Identifiers Identifiers in LabKey SQL may be quoted using double quotes. (Double quotes within an identifier are escaped with a second double quote.) 

SELECT "Physical Exam".*
... 
Lookups Lookups columns are columns that see data in other tables.  They are essentially foreign key columns that can be managed through an intuitive user interface.  See Lookups for details on creating lookup columns. Lookups use a convenient syntax of the form "Table.ForeignKey.FieldFromForeignTable" to achieve what would normally require a JOIN in SQL. Example:

Issues.AssignedTo.DisplayName
String Literals String literals are quoted with single quotes ('). Within a single quoted string, a single quote is escaped with another single quote.

   SELECT * FROM TableName WHERE FieldName = 'Jim''s Item' 
Date/Time Literals

Date and Timestamp (Date&Time) literals can be specified using the JDBC escape syntax

{ts '2001-02-03 04:05:06'}

{d '2001-02-03'}

Search

 

Pages

 

previousnext
 
expand allcollapse all