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 |
|
@HDR.Site |
|
@HDR.Subject |
|
@HDR.EventGroup |
|
@HDR.Event |
|
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 |
|
@ItemGroup |
|
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
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 Functions
- Coding Functions
- Flow Control Functions
- String Functions
- Date & Time Functions
- Other Functions
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.
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 | 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).
RIGHT
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
For date functions, use the YYYY-MM-DD format.
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)
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.
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 |
Raw Format Dates
Date or DateTime as Recorded | Raw Format | |
---|---|---|
1 | 27-Oct-2020 | 2020-10-27 |
2 | ?-Oct-2020 | 2020-10-UN |
3 | ?-?-2020 | 2020-UN-UN |
4 | 27-Oct-2020 10:40 | 2020-10-27T17:40:00.000Z |
5 | 27-Oct-2020 ? | 2020-10-27TUN:UN:UN.UNKZ |
6 | ?-Oct-2020 ? | 2020-10-UNTUN:UN:UN.UNKZ |
7 | ?-?-2020 ? | 2020-UN-UNTUN:UN:UN.UNKZ |
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. 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.
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