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.

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
  • SubjectKey
  • Status
  • ExternalID
  • CasebookVersion
@HDR.EventGroup
  • ID
  • Name
  • SeqNbr
  • Type
  • ExternalID
@HDR.Event
  • ID
  • Name
  • LastModifiedDate
  • SeqNbr
  • Date
  • DateTime
  • Status
  • Frozen
  • Locked
  • Signed
  • ILB
  • ILBReason
  • SDV
  • DMR
  • OID
  • Dynamic
  • EventType
  • ChangeReason
  • PlannedDate
  • ExternalID
  • Version

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
  • SeqNbr
  • Status
  • ILB
  • ILBReason
  • SDV
  • DMR
  • Frozen
  • Locked
  • Signed
  • SDVDate
  • DMRDate
  • FrozenDate
  • LockedDate
  • SignedDate
  • 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.

Note that on subject doesn’t affect any subqueries that are included in the list of Forms used for the query. You must continue to join subqueries explicitly using the where clause.

Known Issue: In the current release, CQL doesn’t support the use of COMPACT when joining on subject.
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

The example query above 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.

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
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 DATAITEM’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 DATAITEM 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 DATAITEM’s Item Definition.

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

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.

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 ATC4D
HLTCD ATC3D
HLGTCD ATC2D
SOCCD ATC1D

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

The following functions are available for dates and times:

DATE_ADD

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

Syntax
DATE_ADD('date', INTERVAL <Number> <Unit>)

For <Unit>, specify DAY, MONTH, or YEAR.

Example
SELECT DATE_ADD(Randomization.Date_of_Randomization, INTERVAL 5 DAY)
FROM Enrollment

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

DATE_SUB

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

Syntax
DATE_SUB('date', INTERVAL <Number> <Unit>)

For <Unit>, specify DAY, MONTH, or YEAR.

Example
SELECT DATE_SUB(Randomization.Date_of_Randomization, INTERVAL 5 DAY)
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, '12-12-2019')
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)

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.

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

Other Functions

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 & UNIONALL

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 only distinct rows. You can use UnionAll to return all result rows, even duplicates.

In the current release, CDB won’t display cell highlighting for listings where the CQL statement uses Union or UnionAll.
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
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
Unsupported Use Cases:

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

  • UnPivot within a subquery
  • UnPivot within Union
  • Multiple UnPivot sections
  • UnPivot on multiple forms
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;
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
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