Clinical Query Language (CQL) Reference

You can use Clinical Query Language (CQL) to access, retrieve, and interact with clinical data in Vault Data Workbench. This guide provides details on how to best utilize CQL, and outlines its syntax, structure, clauses, and operators. Although CQL queries share many syntax elements with Structured Query Language (SQL), CQL statements allow you to perform queries specifically for EDC data using a streamlined, clinical-focused syntax.

Availability: In the current release, Vault Clinical DataBase (CDB) is only available to specific early adopter customers. Contact your Veeva Services representative for details.

When you query data in Data Workbench, it passes a CQL statement that specifies the objects to query in the FROM clause, the fields to retrieve in the SELECT clause, and any optional filters to apply (in the WHERE clause).

The following example query returns all data items from the Demographics and Inclusion Criteria forms in the Cholecap 2019 Phase 1 study.

SELECT *
FROM F.Demographics, F.Inclusion_Criteria

CQL’s Clinical Context

CQL is aware of common clinical data patterns and relationships. This “context” provides a language syntax that is simpler than general purpose query languages like SQL. CQL is aware of the following predefined objects and relationships:

Context Element Description Examples
Study

All clinical data is collected within the context of a Study, which has a predefined structure of time-points and data, as well as predefined workflows for collecting, reviewing, cleaning, and locking that data.

IMG-0314-A, TEL2019-1
Site

A Site where clinical data is collected.

101
Country

The country (represented by the Study Country object in Vault EDC) where a Site is located.

USA, UK
Subject

All clinical data within a clinical trial is collected about a Subject enrolled in that Study.

1001, 1002
Event

All data is collected within the context of a specific collection point. This collection point is an “Event”. This often corresponds with a “visit”.

Enrollment Visit, Visit 1, Visit 2
Header

All of the Forms collected for a given Subject at a given collection point (Event) share the same context. You can query for this context using the header. The header is a quick way to return all context objects that exist above the form in the Workbench’s object hierarchy.

@HDR
Form

The collection of data items collected on a Form.

Vital Signs, Medical History
Data Item

Specific data elements (Items) collected about a Subject at a specific collection point in the Study.

Date of Birth, Systolic Blood Pressure, Eye Color
Properties

All of the context elements listed above have additional properties describing various aspects of the element. These properties can represent the state of the object or basic information, such as the item’s name or the date it was last modified. Most properties of an element are returned by function(), however, some basic properties such as Name, ID, etc. can be returned using dot notation.

Locked(Event 1), Item1.ID

Queryable Data

To create queries in CQL, it’s important to understand what data is queryable and the structure of that data. You can use the DESCRIBE and SHOW keywords to return lists of queryable data with CQL.

Describe

You can create a query with the DESCRIBE keyword to return details about the schema.

CQL supports DESCRIBE for the following data elements:

  • HEADER
  • FORM
  • ITEMGROUP
  • ITEM

When you call DESCRIBE, CQL returns every field within that table or object.

DESCRIBE Form

Calling DESCRIBE for a form returns a list of all items (with the Name and DataType) included on the form.

DESCRIBE Form VITALS

This query returns a list of all of the items on the Vitals form for the Cholecap 2019 - Phase 1 study.

  Name Data Type
Form Form.Name Text(100)
Form.SeqNbr Int
Item Group ItemGroup.Name Text(100)
ItemGroup.SeqNbr Int
Item Pulse Int
Item Systolic_Blood_Pressure Text(255)
Item Diastolic_Blood_Pressure Text(255)
Item Temperature Int

Show

You can also create a query with the SHOW keyword to return a list of possible values. You can apply SHOW to event groups, events, forms, and codelist items. You can also use SHOW to return a list of Studies within the current vault.

SHOW Studies

You can use SHOW to return a list of Studies within the current vault:

SHOW STUDIES

SHOW EVENTGROUPS

You can use SHOW to return a list of Event Groups within the current vault:

SHOW EVENTGROUPS

SHOW EVENTS

You can use SHOW to return a list of Events within the current vault:

SHOW EVENTS

SHOW FORMS

You can use SHOW to return a list of Forms within the current vault:

SHOW FORMS

SHOW CODELIST ITEMS

You can use SHOW to return a list of Codelist Items within the current vault. Note that you must provide the name of a Codelist Definition.

SHOW CODELIST Painscale

Query Syntax & Structure

CQL queries return a tuple: a table of data with a defined order of columns. In order to return a tuple, your CQL query must specify:

  • PROJECTION: The specific data elements that you want to retrieve.
  • SELECTION: The specific data tables in which the data elements in the projection exist.
  • FILTER (Optional): The matching criteria for the Data Elements to be returned (these don’t have to be included in the tuple and can be referenced through the implicit relationships in the study).
  • ORDER (Optional): The order in which the rows are returned in the tuple.

Projection

The PROJECTION of your CQL query specifies which data elements you’re retrieving and the order of those elements in the result set. You can specify the list of fields explicitly using the DATAITEM names, or you can use wildcard shortcuts.

Use a SELECT statement to describe the projection.

Wildcards

You can use a wildcard asterisk on your SELECT statement to return all columns, in layout order, for the associated DATAITEMS within the FORM context specified in the FROM clause.

SELECT *
FROM Demographics

Context-Qualified Asterisk

Instead of a wildcard asterisk, you can use a context-qualified asterisk to return all DATAITEMS within the context of the qualifier.

In the current release, only the FORM and ITEMGROUP qualifiers are supported.

SELECT <Qualifier>.*
FROM <Form_Name>

SELECT Creation_Criteria.*
FROM Demographics

This example query would return all DATAITEMS in the Creation Criteria item group on the Demographics form in the Cholecap 2019 Phase 1 study.

Data Items

Alternatively, you can specify specific DATAITEMS to retrieve. Any DATAITEM that exists on the specified FORM can be selected in the projection. If you only specify one FORM, then you can reference those columns directly. If you specified multiple FORMs, then you must qualify each DATAITEM with the FORM name or alias.

SELECT <Qualifier>.<DataItem>
FROM <Form_Name>

SELECT Demographics.Subject_Date_of_Birth, Enrollment.Randomization_Number
FROM Demographics, Enrollment

This example query would return all values for the Date of Birth item on the Demographics form and the Randomization Number item on the Enrollment form.

Data Functions

You can also specify functions which can be returned as specific columns of the result set. Functions can take DATAITEMS as arguments, as well as many of the contexts listed above.

You can access all the properties of a DATAITEM with a function, except for Name and ID.

You can’t use Item Groups in functions.

For a complete list of functions, accepted arguments, and definitions, see below.

SELECT STATUS(Demographics.Subject_Date_of_Birth)
FROM Demographics

This example query would return the Status of the Date of Birth item on the Demographics form in the Cholecap 2019 Phase 1 study.

Distinct

You can use DISTINCT to return only unique instances of the values in a given column or across a set of columns.

SELECT DISTINCT <Qualifier>.<DataItem>
FROM <Form_Name>

SELECT DISTINCT Dosing.Dosage_Amount
FROM Dosing

This query would return all unique values for the Dosage Amount item on the Dosing form in the Cholecap 2019 Phase 1 study.

In the current release, CDB won’t display cell highlighting for listings where the CQL statement uses DISTINCT.

Aliases (AS)

You can use AS to define aliases for forms and data items in your query. Aliases can be useful when a form or data item has a longer name, and you want to reference it multiple times.

You can provide aliases for specific columns in your projection. If you provide an alias for a data item, Workbench renames the item’s column with the alias in the Listing. Aliases for data items are especially useful when a data item has a longer name, like the example below. Using shorter aliases for data items can help limit the need for horizontal scrolling while viewing your results in their Core Listing.

You can also provide aliases for forms. This is useful you want to reference your form multiple times in a single query. After you set the alias, you need only use the alias, and not the form name, to reference the form.

SELECT <Qualifier>.<DataItem> AS <Item_Alias>
FROM <Form_Name> as <Form_Alias>

SELECT Demographics.Subject_Date_of_Birth as DOB, Enrollment.Randomization_Number_from_IRT as ID
FROM Demographics, Enrollment

This example query would return all values for the Date of Birth item on the Demographics form and the Randomization Number item on the Enrollment form.

Operational Projection Elements

In addition to DATAITEMS, you can query for operational data that may be useful alongside your query results. The most powerful of these operational elements it the Header. The Header is a summary of the most important study metadata that relates to the forms and data items you’re requesting. You can also request information about the Study, Site, Subject, Event Group, and Event.

You can return Header information in any projection using the @HDR variable notation. You can query for @HDR information with a wildcard to retrieve all queryable metadata, or query for specific header information related to a Form.

SELECT @HDR, <Qualifier>.<DataItem>
FROM <Form_Name>

SELECT @HDR.SITE, *
FROM Dosing

The query above would return summary information for the Site.

Header Summaries

You can create specific summaries within a given context, using a predefined set of elements and column order. The following header contexts are available:

  • @HDR.Study
  • @HDR.Site
  • @HDR.Subject
  • @HDR.Event

In the current release, @HDR.EventGroup does not include a summary. You must specify a property to use the Event Group context.

If you use the @HDR variable and the context alone, such as @HDR.Study, CQL returns only a limited set of the available properties.

Header Context Returned Properties
@HDR.Study Study.Name
@HDR.Site Site.Name, Site.PI
@HDR.Subject Subject.Name, Subject.Status
@HDR.Event Event.Name, Event.Date, Event.Status
Header Detail Properties

You can also target specific elements of the header to return in the projection or to use in a filter clause. Append the property name to the @HDR context using dot notation to retrieve that property. For example, use @HDR.Study.Phase to return the Phase for the Study, or use @HDR.Event.Date to return the Event Date for an Event.

The following properties are available for each @HDR context element, using dot notation.

Header Context Available Properties
@HDR.Study
  • ID
  • Name
  • Phase
  • LastModifiedDate
  • Status
  • Locked
  • OID
  • ExternalID
@HDR.Site
  • ID
  • Name
  • Number
  • LastModifiedDate
  • PI
  • Country
  • Status
  • OID
  • ExternalID
@HDR.Subject
  • ID
  • Name
  • LastModifiedDate
  • WithdrawnDate
  • Key
  • Status
  • ExternalID
@HDR.EventGroup
  • ID
  • Name
  • SeqNbr
  • Type
@HDR.Event
  • ID
  • Name
  • LastModifiedDate
  • SeqNbr
  • Date
  • DateTime
  • Status
  • Frozen
  • Locked
  • Signed
  • ILB
  • ILBReason
  • SDV
  • DMR
  • OID
  • Dynamic
  • EventType
  • ChangeReason
  • PlannedDate
  • ExternalID
  • OpenQuery
  • AnsweredQuery
  • ClosedQuery

Form & Item Group Properties

You can target properties of the Form and Item Group in the projection or in a filter clause. Append the property name to the @Form or @ItemGroup using dot notation to retrieve that property. For example, use @Form.CreatedDate to return the Created Date for a Form.

Context Available Properties
@Form
  • Name
  • DataSource
  • SeqNbr
  • Status
  • ILB
  • ILBReason
  • SDV
  • DMR
  • Frozen
  • Locked
  • Signed
  • SDVDate
  • DMRDate
  • FrozenDate
  • LockedDate
  • SignatureDate
  • CreatedDate
  • SubmissionDate
  • SubmissionCount
  • LastModifiedDate
  • Version
  • ID
  • ExternalID
@ItemGroup
  • Name
  • SeqNbr

Selection

Selection (FROM) is the mechanism by which you specify the Names of the Forms on which the projection operates. In the wildcard asterisk, the selection also specifies the order of the projection’s Forms.

In CQL, FROM always refers to the Form context that the data is selected from. You can’t use any other context in the FROM clause.

Selection doesn’t support JOINs or other complex operators. Because of CQL’s clinical context, JOINs and other complex operators aren’t required. You can easily access data from multiple data collection forms and operational data using basic CQL operators and the @HDR variable.

Because the Header is accessible from the @HDR variable, you can also select any elements of the Header by themselves if you don’t need the FORM and DATAITEMS. You can use these types of selections to create results listing only operational data by subject. You can filter this selection without providing a FROM when the projection contains only @HDR. However, to use aliasing and subqueries, you must include a FROM clause.

Join on Subject

By default, CQL joins on both Subject and Event. Because of this, if you select from two Forms that are in different Events, CQL will return a row for each Form, instead of a single row per subject.In this case, you can return a single row for each Subject, containing the data from both Forms, by joining on subject only.

To join on subject, append on subject to the list of form identifiers in the selection. There is no limit to the number of identifiers that you can use, but on subject applies to all forms specified.

CQL returns null for any @HDR.EventGroup or @HDR.Event properties when used with on subject because on subject can return data from multiple Event Groups or Events. If you need to retrieve these properties, you can do so by selecting it with the form alias for each Form.

Known Issue: In the current release, CQL doesn’t support the use of COMPACT when joining on subject.

The example query below would return a single row for each Subject with the @HDR.Subject properties, the Subject Initials, Subject DOB, Height, Weight, Pulse, Systolic Blood Pressure, Diastolic Blood Pressure, and Temperature. If the example query didn’t use join by subject, CQL would return a row for the items on the Demographics form and a second row for the items on the Vitals form.

SELECT @HDR.Subject, creation_criteria.subject_initials, creation_criteria.subject_dob, vitals.height, vitals.weight, vitals.pulse__bpm_, vitals.systolic_bp__hhMg, vitals.diastolic_bp__hhMg, vitals.temperature 
FROM demographics, vitals ON subject
Align & Unalign Modifiers

When using on subject with a Form or Item Group that is repeating, you can choose to return data for joined sequences (aligned) or return data even when the sequences don’t align (unaligned). For example, when showing information from a Demographics form and a repeating Adverse Event form, CQL returns the data from the Demographics form in the rows returned for all Adverse Event form instances for the Subject. This behavior is “unaligned” because it is returning the data even when the sequences aren’t aligned. UNALIGN is the default behavior, so explicitly including this modifier in the CQL statement is optional. In this example, “aligned” behavior would return the data from the Demographics form only for the first row returned for all Adverse Event form instances for the Subject. To use the aligned behavior, include the ALIGN modifier.

Subqueries

CQL supports subqueries.

Subqueries with IN

You can define a subquery that returns a list of values that you can reference within the IN operator of a filter.

SELECT * FROM Randomization
WHERE @HDR.Subject.ID IN (
    SELECT @HDR.Subject.ID FROM Demographics 
    WHERE Creation_Criteria.Sex = 'Female'
)

This query returns all DATAITEMS from the Randomization form for all female subjects.

Subquery Joins

You can also define two SELECT statements and join them together using a subquery, instead of typical JOIN syntax. Use a comma (,) to represent the join, with the second subquery being left-outer-joined to the first. Sub-selects can be aliased, but it’s not required. These joins implicitly include EventId and SubjectId. You can accomplish additional filtering with a filter (WHERE) clause after your subqueries.

SELECT * FROM
    (
        SELECT @HDR.Subject, Creation_Criteria.Subject_Initials, Creation_Criteria.Age_at_Screening as Screening_Age
        FROM Demographics
        WHERE Creation_Criteria.Sex = 'Female'
    ) AS Female_Subjects
    (
        SELECT @HDR.Subject, Creation_Criteria.Subject_Initials, Creation_Criteria.Age_at_Screening as Screening_Age
        FROM Demographics
        WHERE Creation_Crtieria.Sex = 'Male'
    ) AS Male_Subjects
WHERE Female_Subjects.Screening_Age = Male_Subjects.Screening_Age
Subqueries in Functions

You can use subqueries, including @HDR, @Form header, and DATAITEMS, within functions.

SELECT @HDR.Subject, Body_Temperature,
IF (IFNULL(Body_Temperatore,0) < (SELECT AVG(Body_Temperature) FROM Vitals), 'Below Avg', 'Acceptable') AS CompTemp
FROM Vitals
Correlated Subqueries

CQL allows correlated subqueries for filtering results.

SELECT * FROM Demographics
WHERE Age_at_Screening > (
    SELECT AVG(Age_at_Screening) FROM Demographics
)

Filter

You can use filters to reduce the values of the initial CQL result provided by your Projection and Selection. This is optional. The primary filtering mechanism in CQL is a WHERE clause.

WHERE Status(Form) = 'Complete' AND GENDER = 'Male'

Comparison Functions & Operators

The following functions and operators are usable in a WHERE clause:

Operator Definition
AND Logical AND
OR Logical OR
= Equals
!= Does not equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
LIKE Simple pattern matching
NOT LIKE Simple pattern matching
IS Tests a value against a boolean condition
IS NOT Tests a value against a boolean condition
IS NULL Tests a value against a NULL
IS NOT NULL Tests a value against a NULL
IN() Check whether or not a value is within a defined set of values
BETWEEN...AND... Check whether or not a value is within a range of values

Order

You can use an ORDER BY clause to customize the order of your results.

For example:

SELECT @HDR * 
ORDER BY @HDR.Subject.Name ASC, ETHNICITY

Known Issue: In the current release, Workbench doesn’t support the use of ORDER BY for any data items within a repeating item group.

Data Aggregation

CQL can perform basic data aggregation on query results. You can reference any element of the header or any DATAITEM in an aggregate function. Aggregate functions accept wildcards, @ variables, and specific DATAITEMS as arguments.

Aggregate Functions

Operator Definition
AVG() Average of all column values
COUNT() Count (number) of rows in the column
MAX() Maximum (highest) value in the column
MIN() Minimum (lowest) value in the column
SUM() Sum of all values in the column
STDDEV_POP() Population standard deviation
STDDEV_SAMP() Sample standard deviation
VAR_POP() Returns the population standard variance
VAR_SAMP() Returns the sample variance

If you don’t specify an attribute to be counted, CQL treats the request as a count on all columns within that object. This is the equivalent of COUNT(*) on that specific table.

Grouping Results

CQL supports GROUP BY syntax as long as there is at least one aggregate function included in your projection.

SELECT COUNT(@HDR.Event), @HDR.Subject
GROUP BY @HDR.Subject

SELECT @HDR, AVG(SYSTOLIC)
FROM Vitals
GROUP BY @HDR.Subject

Result Structure

CQL results are returned in a specific sequence based on the elements you provide in the Projection. CQL includes three methods for quickly identifying what should be included in the projection: the header, the wildcard, and qualified wildcards. You can specify additional columns that should be returned in the results within you query.

Depending on which DATAITEMS you include in your Projection, there is also a specific order in which execution data is sorted.

Form & Data Item Results

As with SELECT @HDR notation, the remaining FORM and DATAITEMS can be returned using SELECT * or SELECT ItemGroup.*. When using these notations, the result set has a default column order, and rows from this section are returned in a specific sort order after following the sort order applied to the Header (if included in the projection).

If you don’t use wildcard notation in your query, you must specify the columns of data you want returned within your projection. The order of the results then matches the hierarchy for the sort options listed below, based on which elements you included in your projection.

Note that any of the objects included in the sort hierarchy are present, even if that specific column isn’t present, the sort hierarchy rules are still applied.

SELECT * Default Column Order

Columns are laid out starting with the FORM attributes listed below and then following the form’s layout order.

  • Form Name
  • Form SeqNum (only if the Form is repeating)
  • Item Group
  • SeqNbr (only if the Item Group is repeating)
  • Item1
  • Item2
  • ItemN

SELECT ItemGroup.* Default Column Order

Columns are laid out starting with the FORM and Item Group attributes listed below, and then following the item group’s layout order.

  • Form Name
  • Form SeqNum (only if the Form is repeating)
  • Item Group
  • SeqNbr (only if the Item Group is repeating)
  • Item1
  • Item2
  • ItemN

Column Titles

CQL titles each column using a specific pattern:

  • @HDR Columns: Object.Property, e.g. “Site.Name” for @HDR.Site.Name
  • Data Items: Name of the Item Definition
  • Alias: For any aliased data, CQL uses the alias as the column title.

Because Forms and Item Groups can repeat, they follow the same Object.Property notation as Header results: Form.Name, ItemGroup.SeqNbr, etc.

Wide & Compact Results

CQL always returns data items with their @Form and @ItemGroup information prepended to the result set. This is the form header. This information is useful for understanding which Forms and Item Group a given data item was collected in. Beyond the form header, you can arrange data items in a wide or compact format. By default, CQL returns results using wide format.

Wide format means that every data item is returned in its own column for every form or item group it was collected in (in the context of the query).

Compact format means that a data item’s results are presented in a single column, regardless of which form or item group it was collected in. This can be useful for comparing item values.

To return results in compact format, specify COMPACT in your SELECT statement:

SELECT COMPACT @HDR, Creation_Criteria.*
FROM Demographics

This query returns the @HDR summary and DATAITEMS in the Demographics form’s Creation Criteria item group in compact format.

Example: Multiple Forms Reusing an Item

In this example, a Study has two forms, Demographics and Informed Consent, that both use the Date of Birth item.

WIDE
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr Initials Age_at_Screening DOB DOB
Demographics 1 Creation_Criteria 1 CMA 27 02-22-1992 --
Informed_Consent 1 Informed_Consent 1 -- -- -- 02-22-1992
COMPACT
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr Initials Age_at_Screening DOB
Demographics 1 Creation_Criteria 1 CMA 27 02-22-1992
Informed_Consent 1 Informed_Consent 1 -- -- 02-22-1992

Example: Repeating Form

In this example, a study has a repeating Adverse Event form.

WIDE
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr Start_Date End_Date Start_Date End_Date
Adverse_Event 1 AE_Duration 1 05-18-2019 05-23-2019 -- --
Adverse_Event 2 AE_Duration 1 -- -- 11-27-2019 11-29-2019
COMPACT
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr Start_Date End_Date
Adverse_Event 1 AE_Duration 1 05-18-2019 05-23-2019
Adverse_Event 2 AE_Duration 1 11-27-2019 11-29-2019

Example: Repeating Item Group

In this example, a study has a Physical Exam form with a repeating Item Group for Exam by Body Area.

WIDE
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr body_area date_of_exam body_area date_of_exam
physical_exam 1 exam_by_body_area 1 Head 12-11-2019 --- ---
physical_exam 1 exam_by_body_area 2 --- --- Chest 12-11-2019
COMPACT
Form.Name Form.SeqNbr ItemGroup.Name ItemGroup.SeqNbr body_area date_of_exam
physical_exam 1 exam_by_body_area 1 Head 12-11-2019
physical_exam 1 exam_by_body_area 2 Chest 12-11-2019

Log Form Events

Some Events within EDC don’t have an Event Date associated with them. These events are “log” events, and the FORMS within these events are “Log Forms”. Common examples of a log form include Adverse Event and Concomitant Medication forms. Information within these log forms is collected independent of scheduled events.

When a log form is included in the projection, CQL groups these forms under a Log Event. The Log Event is always listed after all other event groups in the result set.

Functions

CQL supports various functions to provide additional information around a given DATAITEM or FORM. You can include any of these functions in the CQL Projection, Filter, and in the Order By clause. CQL also includes a set of functions that are specific to querying clinical data.

Clinical

The following clinical functions are available:

DESCRIPTION

Returns the description provided for the specified data item’s Item Definition.

Syntax
DESCRIPTION(DATAITEM)
Example
SELECT DESCRIPTION(Randomization.Randomization_Number)
FROM Enrollment

This query returns the Description value for the Randomization Number item on the Enrollment form.

FROZEN

Returns true or false based on whether or not the given data item is currently frozen. If this value is NULL, it is inherited from its parents (Item Group and Form).

Syntax
FROZEN(DATAITEM)
Example
SELECT FROZEN(Creation_Criteria.Initials)
FROM Demographics

This query returns true if the Initials item on the Demographics form is frozen. If the Initials item is unlocked, this query returns false.

HELPTEXT

Returns the help text provided for the specified data item’s Item Definition.

Syntax
HELPTEXT(DATAITEM)
Example
SELECT HELPTEXT(Randomization.Randomization_Number)
FROM Enrollment

This query returns the Help Text (the Help Content property for items created in Vault EDC) value for the Randomization Number item on the Enrollment form.

HINTLABEL

Returns the Hint Label provided for the specified data item’s Item Definition.

Syntax
SELECT HINTLABEL(Randomization.Randomization_Number)
FROM Enrollment

This query returns the Hint Label (the Hint Label property for items created in Vault EDC) value for the Randomization Number item on the Enrollment form.

ILB

Returns true or false based on whether or not the given DATAITEM is marked as Intentionally Left Blank. If this value is NULL, it is inherited from its parent form.

Syntax
ILB(DATAITEM)
Example
SELECT @HDR, *
FROM Dosing
WHERE ILB(Dosage_Amount) = FALSE 

This query returns the header summary (@HDR) and all data items (*) from the Dosing form for any subjects where the Dosage Amount item is not marked as Intentionally Left Blank.

ILBREASON

Returns the Change Reason associated with a DATAITEM or FORM that was marked as Intentionally Left Blank.

Syntax
ILBREASON(DATAITEM)
Example
SELECT ILB(Dosage_Amount)
FROM Dosing
WHERE ILB(Dosage_Amount) = TRUE

This query returns the Change Reason for Dosage Amount items on the Dosing form that were marked as intentionally left blank. This query filters the results so that CQL doesn’t return rows for Dosage Amount items that weren’t marked as intentionally left blank.

LABEL

Returns the label provided for the specified DATAITEM’s Item Definition. You can choose to specify an alternate locale to return a localized label. By default, LABEL() returns the label in the current user’s locale. See a list of Vault CMDS locale values here.

Syntax
LABEL(DATAITEM,['locale'])
Example
SELECT LABEL(Randomization.Randomization_Number, 'es_PR')
FROM Enrollment

This query returns localized (for es_PR, or Puerto Rico) item label for the Randomization Number item on the Enrollment form.

LASTMODIFIEDDATE

Returns the date on which the DATAITEM was last modified (for data in Vault EDC, this is based on the Last Modified Date field on the Item object).

Syntax
LASTMODIFIEDDATE(DATAITEM)
Example
SELECT LASTMODIFIEDDATE(Randomization.Randomization_Number)
FROM Enrollment

Returns the date that the Randomization Number item on the Enrollment form was last modified.

LOCKED

Returns true or false based on whether or not the given DATAITEM is currently locked. If this value is NULL, it is inherited from its parents (Item Group and Form).

Syntax
LOCKED(DATAITEM)
Example
SELECT LOCKED(Creation_Criteria.Initials)
FROM Demographics

This query returns true if the Initials item on the Demographics form is locked. If the Initials item is unlocked, this query returns false.

SIGNED

Returns true or false based on whether or not the given DATAITEM has been signed. If this value is NULL, it is inherited from its parents (Item Group and Form).

Syntax
SIGNED(DATAITEM)
Example
SELECT SIGNED(Creation_Criteria.Initials)
FROM Demographics

This query returns true if the Initials item on the Demographics form has been signed. If the Initials item has not been signed, this query returns false.

STATUS

Returns the status of a DATAITEM. You can also use this function to return the status of a form by using dot notation and the @Form variable, instead of STATUS(). See the second example.

Syntax
STATUS(DATAITEM)
Examples
SELECT STATUS(Randomization.Randomization_Number)
FROM Enrollment

This query returns the Status of the Randomization Number item on the Enrollment form.

SELECT @Form.Status
FROM Enrollment

This query returns the Status of the Enrollment form.

UOM

Returns the Unit of Measure (UOM) for the DATAITEM

Syntax
UOM(DATAITEM)
Example
SELECT UOM(Vitals.Weight)
FROM Vitals

This query returns the UOM for the Weight item on the Vitals form.

UOMTRANSLATED

Returns the Unit of Measure (UOM) of the default unit, instead of the user-entered unit (e.g. Fahrenheit instead of Celsius)

Syntax
UOMTRANSLATED(Vitals.Temperature)
Example
SELECT TRANSLATED(Vitals.Temperature)
FROM Vitals

This query returns “Fahrenheit”, the default unit for the Temperature item.

TRANSLATED

Returns the value in the default unit, translated (converted) into the default unit from the user-entered unit

Syntax
TRANSLATED(DATAITEM)
Example
SELECT TRANSLATED(Vitals.Temperature)
FROM Vitals

This query returns the value in fahrenheit (default unit) from the Temperature item on the Vitals form, even if the site user entered the temperature in Celsius.

FormName, FormSeqNbr, ItemGroupName, ItemGroupSeqNbr

Returns the Name and Sequence Number of the Form and Item Group of the data item.

Syntax
FormName(Item), FormSeqNbr(Item), ItemGroupName(Item), ItemGroupSeqNbr(Item)
Example
SELECT FormName(ae_term), FormSeqNbr(ae_term), ItemGroupName(ae_term), ItemGroupSeqNbr(ae_term)
FROM adverse_event

Labs

The following functions are available for use with data from EDC’s Labs module.

SPECIMENTYPE

Returns the Specimen Type of the Analyte specified in the LBTEST column. Use the LBTEST item with this function.

Syntax
SpecimenType(LBTEST)
Example
SELECT LBTEST, SpecimenType(LBTEST) FROM edc.baseline_labs

The example above returns the LBTEST item and the Specimen Type for the test.

TESTINGMETHOD

Returns the Testing Method of the Analyte specified in the LBTEST column. Use the LBTEST item with this function.

Syntax
TestingMethod(LBTEST)
Example
SELECT LBTEST, TestingMethod(LBTEST) FROM edc.baseline_labs

The example above returns the LBTEST item and the Testing Method for the test.

LOINCCODE

Returns the LOINC Code of the Analyte specified in the LBTEST column. Use the LBTEST item with this function.

Syntax
LOINCCode(LBTEST)
Example
SELECT LBTEST, LOINCCode(LBTEST) FROM edc.baseline_labs

The example above returns the LBTEST item and the LOINC Code for the test.

ANALYTECODE

Returns the Analyte Code of the Analyte specified in the LBTEST column. Use the LBTEST item with this function.

Syntax
AnalyteCode(LBTEST)
Example
SELECT LBTEST, AnalyteCode(LBTEST) FROM edc.baseline_labs

The example above returns the LBTEST item and the Analyte Code for the test.

LABLOCTITLE

Returns the Lab Title for the Lab Location. LBLOC returns only the Lab ID. Use the LBLOC item with this function.

Syntax
LabLocTitle(LBLOC)
Example
SELECT LBLOC, LabLocTitle(LBLOC) FROM edc.baseline_labs

The example above returns the Lab ID (LBLOC) and the Lab Location Title for the Lab Location chosen on the Baseline Labs form.

OVERRIDEREASON

Returns the Override Reason that the site user provided when entering a Normal Override. Use the LBOVRDNRLO and LBOVRDNRHI items with this function.

Syntax
OverrideReason(LBOVRDNRLO), OverrideReason(LBOVRDNHRI)
Example
SELECT OverrideReason (LBOVRDNRLO), OverrideReason(LBOVRDNRHI) FROM edc.baseline_labs

Coding

The following functions allow you to retrieve information about the assigned coded terms and status of coding forms. Vault supports coding with the MedDRA and WHODrug (B3 and C3) dictionaries.

If you use these functions on a Form that isn’t configured to use Vault Coder, CQL will return an error.

CODEDTERM

If a coded term exists (the form’s Code Request is in the Coded or Autocoded status), returns the Coded Term for a data item. By default, CQL returns the Preferred Term for the MedDRA dictionary and the Preferred Name for the WHODrug dictionary. You can specify a term level to return another level, such as HLT or LLT.

Syntax
CODEDTERM(Item, 'TermLevel')
Example
SELECT CODEDTERM(ae_term.ae, 'Primary Path') as Primary Path,
       CODEDTERM(ae_term.ae, 'LLT') as LLT,
       CODEDTERM(ae_term.ae, 'PT') as PT,
       CODEDTERM(ae_term.ae, 'HLT') as HLT,
       CODEDTERM(ae_term.ae, 'HLGT') as HLGT,
       CODEDTERM(ae_term.ae, 'SOC') as SOC
FROM adverse_event
Term Levels
MedDRA WHODrug
Primary Path Drug Name
LLT Preferred Name
PT ATC4
HLT ATC3
HLGT ATC2
SOC ATC1

CODEDCODE

If a coded code exists (the form’s Code Request is in the Coded or Autocoded status), returns the Coded Code for a data item. By default, CQL returns the Preferred Term Code _for the MedDRA dictionary and the _Preferred Code for the WHODrug dictionary. You can specify a code level to return another level.

Syntax
CODEDCODE(Item, 'CodeLevel')
Example
SELECT CODEDCODE(medication_therapy.conmed, 'DrugCD'),
       CODEDCODE(medication_therapy.conmed, 'PreferredCD'),
       CODEDCODE(medication_therapy.conmed, 'LLTCD'),
       CODEDCODE(medication_therapy.conmed, 'PTCD'),
       CODEDCODE(medication_therapy.conmed, 'HLTCD'),
       CODEDCODE(medication_therapy.conmed, 'HLGTCD'),
       CODEDCODE(medication_therapy.conmed, 'SOCCD')
FROM concomitant_medication
Code Levels
MedDRA WHODrug
PrimaryPatchCD DrugCD
LLTCD PreferredCD
PTCD ATC4CD
HLTCD ATC3CD
HLGTCD ATC2CD
SOCCD ATC1CD

CODEDDICTREL

Returns the coded dictionary release version assigned to the Form.

Syntax
CODEDDICTREL(Item)
Example
SELECT CODEDDICTREL(ae_term.ae)
FROM adverse_event
SELECT CODEDDICTREL(medication_therapy.conmed)
FROM concomitant_medication

CODEDSTATUS

Returns the Coding Status of the Item (Code Request).

Syntax
CODEDSTATUS(item)
Example
SELECT CODEDSTATUS(ae_term.ae)
FROM adverse_event

CODEDBY

Returns the Coded By (user ID of the user who coded the Code Request) for the Item.

Syntax
CODEDBY(item)
Example
SELECT CODEDBY(ae_term.ae)
FROM adverse_event

LASTCODED

Returns the Last Coded Date (date of the last time the Code Request moved into the Coded or Autocoded status) for the Item.

Syntax
LASTCODED(item)
Example
SELECT LASTCODED(ae_term.ae)
FROM adverse_event

Flow Control

The following flow control functions are available:

IF

Evaluates a logical expression and returns a value if true or false. This function is useful when you’re comparing two values.

Syntax
IF(<Expression>, <Return_Value_True>,<Return_Value_False>)
Example
SELECT IF(Item1=Item2,1,0) as comp
FROM Form1

This query returns “1” if Item 1 is equal to Item 2. If Item 1 is not equal to Item 2, this query returns “0”.

CASE

Evaluates a logical expression and returns a value if true or false. This function is useful when you’re comparing multiple values.

Syntax
CASE WHEN(<Expression>) THEN '<Return_Value>' ELSE '<Return_Value>' END
Example
SELECT CASE WHEN(Item1=Item2) THEN 'a' WHEN Item1 > Item2 THEN 'b' ELSE 'c' END
FROM Form1

This query returns ‘a’ when Item 1 is equal to Item 2, ‘b’ when Item 1 is greater than Item 2, or ‘c’ if neither of those conditions are true.

IFNULL

Evaluates a DATAITEM and returns a value if the DATAITEM is NULL.

Syntax
IFNULL(DATAITEM,<Return_Value>)`
Example
SELECT IF(IFNULL(Item1,0)=Item2,1,0) as comp
FROM Form1

This query returns “1” if Item1 and Item2 are equal or if Item1 is NULL and Item2 is equal to 0. Otherwise, this query returns “0”.

String

The following string functions are available:

CONCAT

Returns the resulting string after concatenating the arguments.

Syntax
CONCAT(DATAITEM, 'string')
Example
SELECT CONCAT(@HDR.Subject.ID,'_',Creation_Criteria.Initials)
FROM Demographics

This query returns a concatenated string of the Subject ID, an underscore, and the Initials item value, for example, 101-1001_CRJ.

LENGTH

Returns the length of the value or DATAITEM passed in the argument.

Syntax
LENGTH(DATAITEM)
Example
SELECT LENGTH(Item1)
FROM Form1

This query returns the length of the Item 1 value from Form 1.

TRIM

Removes the leading and trailing spaces from a DATAITEM.

Syntax
TRIM(DATAITEM)
Example
SELECT TRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

This query returns the Interpretation item’s value from the Baseline Exam form with any leading or trailing spaces removed.

LTRIM

Removes leading spaces from a DATAITEM.

Syntax
LTRIM(DATAITEM)
Example
SELECT LTRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

This query returns the Interpretation item’s value from the Baseline Exam form with any leading spaces removed.

RTRIM

Removes trailing spaces from a DATAITEM.

Syntax
RTRIM(DATAITEM)
Example
SELECT RTRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

This query returns the Interpretation item’s value from the Baseline Exam form with any trailing spaces removed.

UPPER

Converts a DATAITEM to upper case.

Syntax
UPPER(DATAITEM)
Example
SELECT UPPER(Item1)
FROM Form1

This query returns the value of the Initials item as uppercase, e.g. from “cfa” to “CFA”.

LOWER

Converts a DATAITEM to lower case.

Syntax
LOWER(DATAITEM)
Example
SELECT LOWER(Item1)
FROM Form1

This query returns the value of Item 1 as lower case.

LEFT

Returns the leftmost len (number of) characters.

Syntax
LEFT(DATAITEM,len)
Example
SELECT LEFT(Creation_Criteria.Initials,1)
FROM Demographics

This query returns the rightmost character from the Initials item (the subject’s last initial).

Returns the rightmost len (number of) characters.

Syntax
RIGHT(DATAITEM,len)
Example
SELECT RIGHT(@HDR.Subject.ID,4)
FROM Demographics

This query returns the 4 rightmost characters from a Subject ID. In the Cholecap2019_Phase1 study, this is the four digit identifier for a subject, e.g. 1001, that is appended to the site number to make up the subject’s ID (e.g. 104-1001).

Date & Time

CDB supports three different ways of looking at dates and datetimes captured through EDC: imputed, raw, and site normalized. Imputed dates are dates where logic is applied to impute any missing parts (unknowns) in a date or datetime. In EDC, studies can allow the collection of unknown day, month, or hour parts of dates and datetimes. In CDB, when a date contains an unknown, CDB applies first logic to those unknown parts. If the month is unknown, CDB imputes the first month of the year. If the day is unknown, CDB inputs the first hour of the day. By default, all dates and datetimes are returned as whole date and datetimes where the unknown parts are imputed. This is done to make date and datetime comparisons easy for review and cleaning.

CDB can also return the exact representation of the date and datetime as entered with the RawDate() function. This function always returns the value exactly as entered as a string, and it doesn’t substitute any of the unknown parts. Additionally, CDB can also return dates or datetimes as a site normalized value using the SiteNormalizedDate() function. This function returns dates and datetimes adjusted to match the timezone of the site for which they were captured.

The following functions are available for dates and times:

ADDDATE

Adds a number of days (resulting in a later date) from a given date or Date-type DATAITEM.

Syntax
ADDDATE('date', days)
Example
SELECT ADDDATE(Randomization.Date_of_Randomization, 5)
FROM Enrollment

This query returns the date 5 days after the Date of Randomization date value.

SUBDATE

Subtracts a number of days (resulting in an earlier date) from a given date or Date-type DATAITEM.

Syntax
SUBDATE('date', days)
Example
SELECT SUBDATE(Randomization.Date_of_Randomization, 5)
FROM Enrollment

This query returns the date 5 days before the Date of Randomization date value.

DATEDIFF

Returns the difference between two dates in days.

Syntax
DATEDIFF('date', 'date')
Example
SELECT DATEDIFF(Randomization.Date_of_Randomization, '2019-12-31')
FROM Enrollment

This query returns the difference (in days) between the Date of Randomization item’s date value and 12-12-2019.

DATE_FORMAT

Returns a given date in the specified format. See a list of date specifiers below.

Syntax
DATE_FORMAT(<date>,'<format>')
Example
SELECT DATE_FORMAT(@HDR.Event.Date, '%Y-%m-%d')

This query returns the Event Date in the %Y-%m-%d format (e.g. 2019-12)

SITENORMALIZEDDATE

Returns the date or datetime normalized to match the timezone for the Site in which they were entered. For EDC Event Dates and Items, this is the value_normalized__v.

Syntax
SiteNormalizedDate(date)
Example
SiteNormalizedDate(edc.ae_start_datetime)

This example returns the Start Date & Time item on the Adverse Events form in the site’s timezone.

RAWDATE

Returns a given date or datetime in the raw format. In raw format, partial dates and times display without any modifications. See a list of raw-formatted partial dates below.

Syntax
RawDate(<date>)
Example
SELECT RawDate(subject_dob) from EDC.demographics

This query returns the date value from the Subject DOB item in raw format.

SDTMDATEFORMAT

Returns a given date or datetime in the SDTM (ISO 8061) format, automatically handling unknown parts of the date if they exist. The value is returned as a string. See a list of SDTM-formatted partial dates below.

Syntax
SDTMDateFomat(<date>)
Example
SELECT SDTMDateFormat(mh.condition_start_date) from EDC.medical_history

This query returns the date value from the Condition Start Date item in SDTM format, handling any unknowns.

STR_TO_DATE

Given a string and a date format, returns a date. See a list of date specifiers below.

Syntax
SELECT STR_TO_DATE('<string>', '<format>')
Example
SELECT STR_TO_DATE('Jan 01, 2021', '%M %D, %Y')

This query returns January 01, 2021 as a date.

CURDATE

Returns the current date as YYYY-MM-DD.

Example
SELECT CURDATE()
FROM Demographics

This query returns the current date.

NOW

Returns the current date and time as a value in ‘YYYY-MM-DD hh:mm:ss’.

Example
SELECT NOW()
FROM Demographics

This query returns the current date and time.

YEAR

Returns the year from a given date.

Syntax
YEAR('date')
Example
SELECT YEAR('2019-12-12')
FROM Demographics

This function returns “2019”, the year from 2019-12-12.

MONTH

Returns the month from a given date.

Syntax
MONTH('date')
Example
SELECT MONTH('2019-12-12')
FROM Demographics

This function returns “12”, the month from 2019-12-12.

WEEK

Returns week of the year from a given date.

Syntax
WEEK('date')
Example
SELECT WEEK('2019-12-12')
FROM Demographics

This function returns “50”, the week of the year for 2019-12-12.

DAY

Returns the day from a given date.

Syntax
DAY('date')
Example
SELECT DAY('2019-12-12')
FROM Demographics

This function returns “12”, the day from 2019-12-12.

HOUR

Returns the hour, given a time.

Syntax
HOUR('time')
Example
SELECT HOUR('10:05:03')
FROM Demographics

This function returns “10”, the hour from 10:05:03.

MINUTE

Returns the minute, given a time.

Syntax
MINUTE('time')
Example
SELECT MINUTE('10:05:03')
FROM Demographics

This function returns “05”, the minute from 10:05:03.

SECOND

Returns the second, given a time.

Syntax
SECOND('time')
Example
SELECT SECOND('10:05:03')
FROM Demographics

This function returns “03”, the second from 10:05:03.

LAST_DAY

Returns the last day of the month, given a date.

Syntax
LAST_DAY('date'), LAST_DAY(DATAITEM)
Example
SELECT LAST_DAY(@HDR.Event.Date)

This query returns the last day of the month from the Event Date’s month. For example, if the Event Date was 2019-11-15, this query would return “30”, or the last day of November.

Unknown Dates

Workbench supports referencing unknown dates and datetimes, either from Vault EDC or 3rd party data, in CQL queries. Workbench allows the following unknown date and time options:

  • Month, Day, and Time
  • Day and Time
  • Time

By default, Workbench converts the unknown date part to the first day of the month or the first month of the year and the unknown time to 00:00:00. This allows you to treat dates universally without concern over unknown dates and times.

Unknown

Returns the parts of the date that are unknown if the date contains unknown parts (M, D, and T), otherwise returns the string COMPLETE.

Syntax
Unknown(Date)
Example
Unknown(edc.mh_condition_start_date)

In this example, the Condition Start Date item on the Medical History form allows for unknown month, day, and time. If an EDC user entered “07-17-2021 5:15”, the function would return COMPLETE, as the entered date contains no unknowns. If an EDC user entered “06-?-2005 ?”, the function would return D,T because the date has an unknown day and time.

UnknownImpute

This function allows a user to specify how they want to represent unknown parts. The function, given an Item, determines if a date contains unknown parts using the raw date value, and then it applies the provided logic for how to handle the unknown parts. The result is a value with the date data type. If the value doesn’t contain any unknown parts, the function returns the default date.

Syntax
UnknownImpute(Item, 'day impute type', 'month impute type', 'time impute type')
Impute Types:

You must enter each impute type, even if a date or datetime doesn’t allow for those unknowns. Note that the input type must be surrounded with single quotes (‘).

  • Day:
    • FIRST DAY: First day of the month.
    • LAST DAY: Last day of the month.
    • MID DAY: 15th of the month.
  • Month:
    • FIRST MONTH: First month of the year.
    • MID MONTH: June (6)
  • Time:
    • FIRST HOUR: 00:00 24hr
    • LAST HOUR: 23:59 24hr
    • MID HOUR: 12:00 24hr
Example
UnknownImpute(edc.ae_start_datetime, 'FIRST DAY', 'FIRST MONTH', 'FIRST HOUR')

In this example, the AE Start DateTime item allows unknowns for time. This query returns the AE Start DateTime with the unknown time set to 00:00. For example, if the entered datetime was “07-22-2021 ?”, CQL would return “07-22-2021 00:00”.

Date & Time Specifiers

The following date and time specifiers are available in CQL:

Specifier Definition
%r Time, 12-hour clock (hh:mm:ss, followed by AM or PM)
%T Time, 24-hour clock (hh:mm:ss)
%I Hour, 12-hour clock, with leading zeroes (01..12)
%l Hour, 12-hour clock, without leading zeroes (1..12)
%k Hour, 24-hour clock, with leading zeroes (01..24)
%i Minutes, numeric (00..59)
%S Seconds (00..59)
%s Seconds (00..59), same as %S
%f Microseconds (000000..999999)
%p AM or PM
%d Day of the month, numeric (00..31)
%e Day of the month, numeric, without leading zeroes (0..31)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd...)
%j Day of the year (001..366)
%w Day of the week (0 is Sunday..6 is Saturday)
%W Weekday name (Sunday..Saturday)
%a Weekday name, abbreviated (Sun..Sat)
%M Month name (January..December)
%b Month name, abbreviated (Jan..Dec)
%m Month, numeric (00..12)
%Y Year, numeric, 4 digits
%y Year, numeric, 2 digits
%U Week (00..53), where Sunday is the first day of the week, WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week, WEEK() mode 1
%V Week (00..53), where Sunday is the first day of the week, WEEK() mode 2; used with %X
%v Week (01..53), where Monday is the first day of the week, WEEK() mode 3; used with %x
%X Year for the week, numeric, where Sunday is the first day of the week; used with %V
%x Year for the week, numeric, where Monday is the first day of the week; used with %v
%% A literal % character
% x x, for any "x" not listed above

Raw Format Dates

Date or DateTime as Recorded Raw Format
1 27-Oct-2020 27-Oct-2020
2 ?-Oct-2020 UN-Oct-2020
3 ?-?-2020 UN-UNK-2020
4 27-Oct-2020 10:40 27-Oct-2020 10:40
5 27-Oct-2020 ? 27-Oct-2020 UN:UN
6 ?-Oct-2020 ? UN-Oct-2020 UN:UN
7 ?-?-2020 ? UN-UNK-2020 UN:UN

SDTM Format Dates

Date or DateTime as Recorded Interval of Uncertainty Nominal Date/Time (--DTC)
1 March 15, 2020 13:14:17 Complete date 2020-03-14T13:14:17
2 March 15, 2020 13:14 Unknown seconds 2020-03-15T13:14
3 March 15, 2020 13 Unknown minutes 2020-03-15T13
4 March 15, 2020 Unknown time 2020-03-15
5 March, 2020 Unknown day 2020-03
6 2020 Unknown month 2020

Other

The following functions are also available for use in CQL queries:

CAST

Cast takes a data item or expression argument and returns the result in the specified data type.

Syntax
CAST(item AS data_type) 
CAST (expression AS data_type

The following data types are available for use within CAST functions:

  • Binary
  • CHAR
  • DATE
  • DATETIME
  • TIME
  • DECIMAL
  • SIGNED
  • UNSIGNED
Example
SELECT CAST(Exclusion_Criteria.Hypersensitivity_Constituents as CHAR)
FROM Demographics

This query returns the codelist item value (Y or N) from the Hypersensitivity Constituents codelist-type Item as text (char).

FIELD

Returns the index, or position, of an item value within a list of strings. This is useful for customizing the order of a list of possible values within a result set.

Syntax
FIELD(DATAITEM, '<string1>', '<string2>','<stringN>')
Example
SELECT @HDR.Subject, Route, Dosage_Amount
FROM Dosing
ORDER BY FIELD(Route, 'Oral', 'Nasal', 'Topical')

This query returns the subject-level header information and the Route and Dosage Amount item values, ordered by the Route from Oral to Nasal to Topical.

UNION

Union combines the results of multiple SELECT statements into a single set of results. CQL uses the column names listed in the first SELECT statement as the column names. For a column in the first SELECT statement, the corresponding column in the subsequent SELECT statements must be of the same data type. For example, if the second column of the first statement is an integer, the second column of the second SELECT statement must also be an integer.

UNION returns all possible result rows from queries in the union, as does UNION ALL. You can use UNION DISTINCT to retrieve only unique result rows from queries in the union. UNION DISTINCT only removes duplicate rows where the duplication is a 100% match.

Syntax
SELECT ItemGroup.Item, ItemGroup.Item FROM Form1
UNION
SELECT ItemGroup.Item, ItemGroup.Item FROM Form2
Example
SELECT test.date_collected, test.rbc, test.wbc FROM LocalLabs.Hematology
UNION
SELECT chem.date_collected, chem.total_protein, chem.urea_nitrogen FROM CentralLabs.Chemistry

UNPIVOT

UnPivot is a relational operator that you can use to convert a table’s columns into column values. You can’t include any @HDR values in an UnPivot or reference any unpivoted Items in a WHERE, GROUP BY, or ORDER BY clause.

In the current release, CDB won’t display cell highlighting for listings where the CQL statement uses UnPivot.

Syntax
SELECT up.@HDR.Subject.Name, up_items
FROM (
    SELECT @HDR.Subject.Name, itemA, itemB, itemC, itemD) up
UNPIVOT (
    up_items
    FOR items in (itemA, itemB, itemC, itemD)
) as unpivot_table
Example: Unpivot
SELECT up.@HDR.Subject.Name up_items
FROM (
	SELECT @HDR.Subject.Name, body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_ FROM Vitals) up
UNPIVOT (
up_items 
FOR items in (body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_)
) as unpivot_table
Example: Multiple Unpivot Sections
SELECT @HDR.Subject.Name, @HDR.Event, Body_Temperature, vsResult, vsTest, VSSTAT, HIDe 
FROM ( SELECT @HDR.Subject.Name, Body_Temperature, Height, Weight, ILB(Body_Temperature), ILB(Height), ILB(Weight) From EDC.Vitals) main
UNPIVOT (vsResult FOR vsTest in (Body_Temperature, Height, Weight)) as result
UNPIVOT (VSSTAT FOR HIDe in (ILB(Body_Temperature), ILB(Height), ILB(Weight))) as ilb

Example: UnPivot on Multiple Forms:
SELECT @HDR.Subject.Name, d.MHTERM, b.Please_specify_abnormality AS term, up_item.vs_result, up_item.vs_test
FROM (SELECT body_temperature, height, weight, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_ FROM vitals) AS main
UNPIVOT (vs_result FOR vs_test IN (body_temperature, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_)) AS up_item,
baseline_chest_x-ray_evaluation b, demographics d
WHERE main.@HDR.Subject.ID = b.@HDR.Subject.ID 
AND main.@HDR.Subject.ID = d.@HDR.Subject.ID
Unsupported Use Cases:

The following use cases are not supported in the current release:

  • UnPivot within a subquery
  • UnPivot within Union
  • Referencing @Form or @ItemGroup properties within UnPivot
UnPivot within a Subquery:
SELECT @HDR.Subject.Name, creation_criteria.subject_initials, creation_criteria.subject_dob, up.*
FROM demographics.creation_criteria
(SELECT @HDR.Subject.Name, up_item.vs_test, up_item.vs_result
FROM (SELECT body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_ FROM Vitals) as main
UNPIVOT (vs_result FOR vs_test IN (body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_)) AS up_item) AS up
WHERE d.@HDR.Subject.Name = up.@HDR.Subject.Name
UnPivot within Union:
SELECT @HDR.Subject.Name, @HDR.Event.Name, up_item.*
FROM (SELECT body_temperature, height, weight, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_ FROM vitals) AS main
UNPIVOT (vs_result FOR vs_test IN (vody_temperature, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_)) AS up_item
UNION ALL
SELECT @HDR.Subject.Name, @HDR.Event.Name, up_item.*
FROM (SELECT WBC, RBC, HEMOGLOBIN, HEMATOCRIT, PLATELET, NEUTROPHILS FROM hematology) AS main
UNPIVOT (vs_result FOR vs_test IN (WBC, RBC, HEMOGLOBIN, HEMATOCRIT, PLATELET, NEUTROPHILS)) AS up_item;

Other Available Functions

The following SQL functions are also available for use within CQL:

Function Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ADDTIME() Add time
ASCII() Return numeric value of left-most character
ASIN() Return the arc sine
ATAN() Return the arc tangent
ATAN2(), ATAN() Return the arc tangent of the two arguments
AVG() Return the average value of the argument
BIN() Return a string containing binary representation of a number
BINARY Cast a string to a binary string
BIT_AND() Return bitwise AND
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
BIT_OR() Return bitwise OR
BIT_XOR() Return bitwise XOR
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CHAR() Return the character for each integer passed
CHAR_LENGTH() Return number of characters in argument
CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
CHARSET() Return the character set of the argument
COALESCE() Return the first non-NULL argument
COERCIBILITY() Return the collation coercibility value of the string argument
COLLATION() Return the collation of the string argument
CONCAT_WS() Return concatenate with separator
CONV() Convert numbers between different number bases
CONVERT() Cast a value as a certain type
CONVERT_TZ() Convert from one time zone to another
COS() Return the cosine
COT() Return the cotangent
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
DEGREES() Convert radians to degrees
ELT() Return string at index number
EXP() Raise to the power of
EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
EXTRACT() Extract part of a date
FIND_IN_SET() Index (position) of first argument within second argument
FLOOR() Return the largest integer value not greater than the argument
FORMAT() Return a number formatted to specified number of decimal places
FOUND_ROWS() For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
FROM_BASE64() Decode base64 encoded string and return result
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as a date
GET_FORMAT() Return a date format string
GREATEST() Return the largest argument
GROUP_CONCAT() Return a concatenated string
HEX() Hexadecimal representation of decimal or string value
INSERT() Insert substring at specified position up to specified number of characters
INSTR() Return the index of the first occurrence of substring
INTERVAL() Return the index of the argument that is less than the first argument
ISNULL() Test whether the argument is NULL
LCASE() Synonym for LOWER()
LEAST() Return the smallest argument
LN() Return the natural logarithm of the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
LOCATE() Return the position of the first occurrence of substring
LOG() Return the natural logarithm of the first argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
LPAD() Return the string argument, left-padded with the specified string
MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MAX() Return the maximum value
MICROSECOND() Return the microseconds from argument
MID() Return a substring starting from the specified position
MIN() Return the minimum value
MOD() Return the remainder
MONTHNAME() Return the name of the month
NAME_CONST() Cause the column to have the given name
NULLIF() Return NULL if expr1 = expr2
OCT() Return a string containing octal representation of a number
OCTET_LENGTH() Synonym for LENGTH()
ORD() Return character code for leftmost character of the argument
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
PI() Return the value of pi
POSITION() Synonym for LOCATE()
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
QUARTER() Return the quarter from a date argument
QUOTE() Escape the argument for use in an SQL statement
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
RANDOM_BYTES() Return a random byte vector
REGEXP Whether string matches regular expression
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
ROUND() Round the argument
ROW_COUNT() The number of rows updated
RPAD() Append string the specified number of times
SEC_TO_TIME() Converts seconds to 'hh:mm:ss' format
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SPACE() Return a string of the specified number of spaces
SQRT() Return the square root of the argument
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
STRCMP() Compare two strings
SUBSTR() Return the substring as specified
SUBSTRING() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
SUBTIME() Subtract times
SUM() Return the sum
SYSDATE() Return the time at which the function executes
TAN() Return the tangent of the argument
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_BASE64() Return the argument converted to a base-64 string
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
TRUNCATE() Truncate to specified number of decimal places
UCASE() Synonym for UPPER()
UNHEX() Return a string containing hex representation of a number
UNIX_TIMESTAMP() Return a Unix timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
UUID() Return a Universal Unique Identifier (UUID)
UUID_SHORT() Return an integer-valued universal identifier
VALUES() Define the values to be used during an INSERT
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
WEIGHT_STRING() Return the weight string for a string
YEARWEEK() Return the year and week