SELECT LCASE(FirstName) AS Name
    SELECT LCASE(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

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,

    SELECT ParticipantId, COUNT(Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY ParticipantId

    SELECT *
    FROM "Physical Exam"
    ORDER BY Created DESC LIMIT 10

Troubleshooting: "Why is the ORDER BY clause not working as expected?"

1. Check to ensure you are sorting by a SELECT column (preferred) or an alias of an expression. Syntax like including the table name (i.e. ...ORDER BY table.columnName ASC) is an expression and should be aliased in the SELECT statement instead (i.e. SELECT table.columnName AS C ... ORDER BY C

2. When authoring queries in LabKey SQL, the query is typically processed as a subquery within a parent query. This parent query may apply it's own sorting overriding the ORDER BY clause in the subquery. This parent "view layer" provides default behavior like pagination, lookups, etc. but may also unexpectedly apply an additional sort.

Two recommended solutions for more predictable sorting:
(A) Define the sort in the parent query using the grid view customizer. This may involve adding a new named view of that query to use as your parent query.
(B) Use the "sort" property in the selectRows API call.

    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.

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...

VALUES (1, 'one'), (2, 'two'), (3, 'three') AS t;

Define a "common table expression" which functions like a subquery or inline view table. Especially useful for recursive queries.

Usage Notes: If there are UNION clauses that do not reference the common table expression (CTE) itself, the server interprets them as normal UNIONs. The first subclause of a UNION may not reference the CTE. The CTE may only be referenced once in a FROM clause or JOIN clauses within the UNION. There may be multiple CTEs defined in the WITH. Each may reference the previous CTEs in the WITH. No column specifications are allowed in the WITH (as some SQL versions allow).

Exception Behavior: Testing indicates that PostgreSQL does not provide an exception to LabKey Server for a non-ending, recursive CTE query. This can cause the LabKey Server to wait indefinitely for the query to complete.  

A non-recursive example:

WITH AllDemo AS
  (
     SELECT *
     FROM "/Studies/Study A/".study.Demographics
     UNION
     SELECT *
     FROM "/Studies/Study B/".study.Demographics
  )
SELECT ParticipantId from AllDemo

A recursive example: In a table that holds parent/child information, this query returns all of the children and grandchildren (recursively down the generations), for a given "Source" parent.

PARAMETERS
(
    Source VARCHAR DEFAULT NULL
)

WITH Derivations AS 
( 
   -- Anchor Query. User enters a 'Source' parent 
   SELECT Item, Parent
   FROM Items 
   WHERE Parent = Source
   UNION ALL 

   -- Recursive Query. Get the children, grandchildren, ... of the source parent
   SELECT i.Item, i.Parent 
   FROM Items i INNER JOIN Derivations p  
   ON i.Parent = p.Item 
) 
SELECT * FROM Derivations

Supplies the difference in age between the two dates, calculated in years.

The interval indicates the unit of age measurement, either SQL_TSI_MONTH or SQL_TSI_YEAR.

CASE can be used to test various conditions and return various results based on the test. You can use either simple CASE or searched CASE syntax. In the following examples "value#" indicates a value to match against, where "test#" indicates a boolean expression to evaluate. In the "searched" syntax, the first test expression that evaluates to true will determine which result is returned. Note that the LabKey SQL parser sometimes requires the use of additional parentheses within the statement.

    CASE (value) WHEN (value1) THEN (result1) ELSE (result2) END
    CASE (value) WHEN (value1) THEN (result1) WHEN (value2) THEN (result2) ELSE (resultDefault) END
    CASE WHEN (test1) THEN (result1) ELSE (result2) END
    CASE WHEN (test1) THEN (result1) WHEN (test2) THEN (result2) WHEN (test3) THEN (result3) ELSE (resultDefault) END

Example:

    SELECT "StudentName",
    School,
    CASE WHEN (Division = 'Grades 3-5') THEN (Scores.Score*1.13) ELSE Score END AS AdjustedScore,
    Division
    FROM Scores 

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

- PostgreSQL & 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(score_1, score_2, score_3) As HIGH_SCORE
FROM MyAssay 

LabKey SQL extension function. Returns a module property, based on the module and property names. For details see LabKey SQL Utility Functions. 

Returns the tangent of the value.

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

Finds the difference between two timestamp values at a specified interval. The interval must be surrounded by quotes.

Example: TIMESTAMPDIFF('SQL_TSI_DAY', SpecimenEvent.StorageDate, SpecimenEvent.ShipDate)

Note that PostgreSQL does not support the following intervals:

SQL_TSI_FRAC_SECOND
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

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

 character_length(value),  char_length(value)

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

SUBSTR('char_sequence', 5, 2) returns '_s' 

SELECT json_each('{"a":"foo", "b":"bar"}') AS Value

-- line comment 1
-- line comment 2
/* block comment 1
    block comment 2 */
SELECT ... 

Issues.AssignedTo.DisplayName

   SELECT * FROM TableName WHERE FieldName = 'Jim''s Item' 

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