Clinical Query Language (CQL) Reference

You can use Clinical Query Language (CQL) to access, retrieve, and interact with clinical data in Veeva Clinical DataBase (CDB). 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.

When you query data in CDB 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:

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
• REFERENCE_OBJECT

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.

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 Study:

SHOW EVENTGROUPS

SHOW EVENTS

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

SHOW EVENTS

SHOW FORMS

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

SHOW FORMS

SHOW CODELIST ITEMS

You can use SHOW to return a list of Codelist Items within a given Codelist. Note that you must provide the name of a codelist-type data item.

SHOW CODELIST <DATAITEM>

SHOW VIEWS

You can use SHOW to return a list of Views within your Study.

SHOW Views

SHOW Reference

You can use SHOW to return a list of Reference Objects within your Study.

SHOW Reference

System Listings

You can access System Listings via CQL using CALL. System Listings are listings generated for raw-type exports that contain contextual information about the study and collected data.

• Sys_Sites: CALL Sys_Sites
• Sys_Subjects: CALL Sys_Subjects
• Sys_Events: CALL Sys_Events
• Sys_Forms: CALL Sys_Forms
• Sys_ILB: CALL Sys_ILB
• Sys_Links: CALL Sys_Links

Views

You can access Views via CQL using SHOW VIEWS. This returns a list of all existing Views for the current Study. Views are specialized listings. You can also reference Views in the selection when writing CQL for use with another View. To do so, use the Name (Title) of the View as the source.

CQL Limitations when Defining Views

The following CQL functionality isn’t supported when defining a View:

• select *
• UnPivot()
• Duplicate column aliases
• @Form attributes other than @Form.SeqNbr in the projection
• @ItemGroup attributes other than @ItemGroup.SeqNbr in the projection

CQL Limitations when Referencing Views

The following are limitations when referencing a View in a custom listing:

• Referencing a View inside another View isn’t supported.
• Inclusion of a view’s @Form and @ItemGroup attributes in the projection or filter (WHERE clause) isn’t supported.
• If using On Subject ALIGN, the View must not use UNALIGN.
• When joining a View with another Form or View using On Subject, the View must use On Subject.

On Subject Joins in Views

Views support On Subject ALIGN and On Subject UNALIGN when a joining a View with another Form or View.

To perform an on subject join between a View and a Form, the view’s View Definition must contain On Subject. If not, CQL returns the following error:

“View can only be joined in the same way as its definition. Please ensure On Subject alignment is the same between View definition and View Reference.”

View Definition Using On Subject ALIGN

The View Definition below retrieves the medications that subjects are taking for an Adverse Event. This View is called “View_AE_CM\OnSubjectALIGN”:

select @HDR.Site.Name, @HDR.Subject.Name,
  @Form.SeqNbr, @ItemGroup.SeqNbr,
  ae.AETERM, ae.AESTDAT, ae.AEONGO, ae.AEENDAT, ae.AESEV,
  cm.CMTRT, cm.CMSTDAT, cm.CMONGO, cm.CMENDDAT
from AdverseEvent as ae, ConcomitantMedication as cm On Subject ALIGN
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))
and AETERM is not null 
order by @HDR.Subject.Name, @Form.SeqNbr

Now, this View can be joined with another Form, VitalSigns, to get vitals such as Height, Weight, or BMI for these subjects. This join can be done using both On Subject ALIGN and On Subject UNALIGN.

select @HDR.Site.Name, @HDR.Subject.Name,
  @Form.SeqNbr, @ItemGroup.SeqNbr,
  algn.AETERM, algn.AESTDAT, algn.AEONGO, algn.AEENDAT, algn.AESEV,
  algn.CMTRT, algn.CMSTDAT, algn.CMONGO, algn.CMENDDAT,
  vtls.HEIGHT, vtls.WEIGHT, vtls.BMI, vtls.SYSBP
from View_AE_CM_OnSubjectALIGN as algn, VitalSigns as vtls On Subject ALIGN
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))

select @HDR.Site.Name, @HDR.Subject.Name,
  @Form.SeqNbr, @ItemGroup.SeqNbr,
  algn.AETERM, algn.AESTDAT, algn.AEONGO, algn.AEENDAT, algn.AESEV,
  algn.CMTRT, algn.CMSTDAT, algn.CMONGO, algn.CMENDDAT,
  vtls.HEIGHT, vtls.WEIGHT, vtls.BMI, vtls.SYSBP
from View_AE_CM_OnSubjectALIGN as algn, VitalSigns as vtls On Subject UNALIGN
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))

View Definition Using On Subject UNALIGN

The View Definition below retrieves the medications that subjects are taking for an Adverse Event. This View is called “View_AE_CM\OnSubjectUNALIGN”:

select @HDR.Site.Name, @HDR.Subject.Name,
  @Form.SeqNbr, @ItemGroup.SeqNbr,
  ae.AETERM, ae.AESTDAT, ae.AEONGO, ae.AEENDAT, ae.AESEV,
  cm.CMTRT, cm.CMSTDAT, cm.CMONGO, cm.CMENDDAT
from AdverseEvent as ae, ConcomitantMedication as cm On Subject UNALIGN
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))
and AETERM is not null 
order by @HDR.Subject.Name, @Form.SeqNbr

Views that contain On Subject UNALIGN in their definition can only be joined to another Form using On Subject UNALIGN, as shown below:

select @HDR.Site.Name, @HDR.Subject.Name,
  @Form.SeqNbr, @ItemGroup.SeqNbr,
  unalgn.AETERM, unalgn.AESTDAT, unalgn.AEONGO, unalgn.AEENDAT, unalgn.AESEV,
  unalgn.CMTRT, unalgn.CMSTDAT, unalgn.CMONGO, unalgn.CMENDDAT,
  vtls.HEIGHT, vtls.WEIGHT, vtls.BMI, vtls.SYSBP
from View_AE_CM_OnSubjectALIGN as unalgn, VitalSigns as vtls On Subject UNALIGN
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))

Reference Objects

To retrieve a list of all reference objects for your Study, use the SHOW keyword:

SHOW Reference

Then, you can use the DESCRIBE keyword to retrieve a list of Items on a given reference object:

DESCRIBE Reference <Reference_Object_Name>

Use the ReferenceRelations keyword to retrieve a table of key value pairs for the following properties: reference_object_name,related_form, related_item, and related_key.

ReferenceRelations <Reference_Object_Name>

For text-type reference object Items, CQL supports the following functions:

• CONCAT
• LENGTH
• TRIM
• LTRIM
• RTRIM
• UPPER
• LOWER
• LEFT
• RIGHT

For date-type reference object Items, CQL supports the following functions:

• ADDDATE
• SUBDATE
• DATEDIFF
• DATE_FORMAT
• SITENORMALIZEDDATE
• RAWDATE
• SDTMDATEFORMAT
• STR_TO_DATE
• CURDATE
• NOW
• YEAR
• MONTH
• WEEK
• DAY
• HOUR
• MINUTE
• SECOND
• LAST_DAY

CQL Limitations for Reference Objects

CQL limitations apply when working with reference objects.

The following functions are not available for use with reference data:

• FROZEN
• ILB
• LABEL
• LASTCODED
• LASTMODIFIEDDATE
• LOCKED
• SIGNED
• STATUS

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.

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.

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

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 Detail Properties

You can also target specific elements of the header to return in the selection 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.

Form & Item Group Properties

You can target properties of the Form and Item Group in the selection 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.

Form Link Items

You can target Items used in Item to Form linking using the @FormLink notation. The Form that contains the FormLink item is the Linking Form. The FormLink item is linked to Linked Form. In order to use the @FormLink notation, you must provide the name of the FormLink item on the Linking Form and the Item you want to target on the Linked Form.

@FormLink.<FormLink-Name>.<Item-Name>

If the Item in the Linked Form is used across more than one Item Group, then you must also provide the Name of the Item Group.

@FormLink.<FormLink-Name>.<ItemGroup-Name>.<Item-Name>

If the Linking Form uses the same FormLink Item across multiple Item Groups, then you must specify the Name of the Item Group FormLink.

@FormLink.<ItemGroup-Name-FormLink>.<FormLink-Name>.<ItemGroup-Name>.<Item-Name>

The following example assumes that there are 2 FormLinks on the AE form: ae_to_cm and ae_to_mh. You can access Items in the Linked Form using the following CQL:

select  @HDR.Subject.Name, AETERM, AESTDAT, AEONGO,@FormLink.ae_to_cm.CMTRT, @FormLink.ae_to_cm.CMSTDAT, @FormLink.ae_to_cm.CMONGO, @FormLink.ae_to_cm.CMENDAT,@FormLink.ae_to_mh.MHTERM, @FormLink.ae_to_mh.MHSTDAT, @FormLink.ae_to_mh.MHONGO, @FormLink.ae_to_mh.MHENDAT
from `EDC`.`AE`
where @For.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v') 

The FormLink ae_to_cm is between the ae_to_cm item and the CM form. You can access the CMONGO item in the Linked Form CM by using @FormLink.ae_to_cm.CMONGO.

The FormLink ae_to_mh is between the ae_to_mh item and the MH form. You can access the MHSTDAT item in the Linked Form MH by using @FormLink.ae_to_mh.MHSTDAT.

You can use Items denoted by the @FormLink notation in the WHERE clause, Order By clause and Group By clause.

The following CQL features are supported with FormLink Items:

• UNION and UNION ALL
• On Subject ALIGN and UNALIGN
• Subqueries
• UNPIVOT

Query Properties

YOu can target properties of Queries in the selection or in a filter clause. Append the property name to @QRY or @QRYMSG using dot notation to retrieve that property. For example, use @QRY.ID to retrieve the ID of the Query.

@QRY

The following properties are querable for the Query object using @QRY:

@QRYMSG

The following properties are queryable for the Query Message object using @QRYMSG:

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.

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

Join on Subject

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

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

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

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

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

Align & Unalign Modifiers

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

Advanced Joins

CQL also supports advanced (directional) joins. With advanced joins, you can join forms without first joining to the @HDR.

LEFT JOIN: All records from the first (left) table and any matching data from the second (right) RIGHT JOIN: All records from the second (right) table and any matching data from the first (left) INNER JOIN: Only records from both tables where a match is found

In the current release, CQL doesn’t support outer joins.

Example: Left Join between Forms

In the CQL statement below, there is a LEFT JOIN between the AE and CM forms, based on the form definition attribute @Form.Subject. Note that Subject.Name is part of the Form Definition and doesn’t come from the @HDR.

select @Form.Subject.Name, @Form.Event.Name,
       AETERM, AESTDAT, CMTRT, CMSTDAT
from AE as ae LEFT JOIN CM as cm
     ON ae.@Form.Subject.Name = cm.@Form.Subject.Name
where @Form.Status = 'submitted__v'

This works without joining to the @HDR because the join condition is based on a @Form attribute (@Form.Subject.Name) and the projection doesn’t contain any @HDR attributes.

Example: Left Join between Forms & @HDR

The CQL query below requires a join with the @HDR since the projection has @HDR attributes. The query below is a LEFT JOIN between @HDR and AE based on Event.ID - the output of this LEFT JOIN is then LEFT JOINed again with CM based on Event.ID and Subject.ID.

In the CQL statement below, the projection contains @HDR attributes, so a join with the @HDR is required. This is a LEFT JOIN between the @HDR and the AE form based on @Form.Event.ID. The output of that join is then joined again with a LEFT JOIN with the CM form based on @Form.Event.ID and @Form.Subject.ID.

select @HDR.Subject.Name, @HDR.Site.Name,
       AETERM, AESTDAT, CMTRT, CMTERM
from @HDR LEFT JOIN AE as ae ON @HDR.Event.ID = ae.@Form.Event.ID
          LEFT JOIN CM as cm ON @HDR.Event.ID = cm.@Form.Event.ID and ae.@Form.Subject.ID = cm.@Form.Subject.ID

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:

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

Comments

If needed, you can also include comments in your CQL statement. To include a comment, enter two dashes and a space (-- ), followed by your comment.

SELECT @HDR, * from EDC.adverse_events
WHERE @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')

-- comment text

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

Because WHERE isn’t supported for use with aggregate functions, you can use the HAVING keyword instead. To use HAVING, your CQL projection must have at least one aggregate function.

SELECT COUNT(@HDR.Subject.Name), @HDR.Site.Number
FROM EDC.demographics
GROUP BY @HDR.Site.Number
HAVING COUNT(@HDR.Subject.Name) > 100

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

COMPACT

Example: Repeating Form

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

WIDE

COMPACT

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

COMPACT

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.

Syntax

HELPTEXT(DATAITEM)

Example

SELECT HELPTEXT(Randomization.Randomization_Number)
FROM Enrollment

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

HINTLABEL

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

Syntax

SELECT HINTLABEL(Randomization.Randomization_Number)
FROM Enrollment

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

ILB

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

Syntax

ILB(DATAITEM)

Example

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

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

ILBREASON

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

Syntax

ILBREASON(DATAITEM)

Example

SELECT ILBREASON(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.

CODELABEL

Returns the Label for the Codelist Item selected for the codelist-type data item.

Syntax

CODELABEL(DATAITEM)

Example

SELECT CODELABEL(adverse_event.AEACTN)
FROM Adverse_Event

This query returns the Label of the Codelist Item selected for the AEACTN (Action Taken with Study Drug) codelist item.

LASTMODIFIEDDATE

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

Syntax

LASTMODIFIEDDATE(DATAITEM)

Example

SELECT LASTMODIFIEDDATE(Randomization.Randomization_Number)
FROM Enrollment

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

LOCKED

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

Syntax

LOCKED(DATAITEM)

Example

SELECT LOCKED(Creation_Criteria.Initials)
FROM Demographics

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

SIGNED

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

Syntax

SIGNED(DATAITEM)

Example

SELECT SIGNED(Creation_Criteria.Initials)
FROM Demographics

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

STATUS

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

Syntax

STATUS(DATAITEM)

Examples

SELECT STATUS(Randomization.Randomization_Number)
FROM Enrollment

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

SELECT @Form.Status
FROM Enrollment

This query returns the Status of the Enrollment form.

UOM

Returns the Unit of Measure (UOM) for the DATAITEM

Syntax

UOM(DATAITEM)

Example

SELECT UOM(Vitals.Weight)
FROM Vitals

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

UOMTRANSLATED

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

Syntax

UOMTRANSLATED(Vitals.Temperature)

Example

SELECT TRANSLATED(Vitals.Temperature)
FROM Vitals

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

TRANSLATED

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

Syntax

TRANSLATED(DATAITEM)

Example

SELECT TRANSLATED(Vitals.Temperature)
FROM Vitals

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

FILL

Returns a specified fill value from an Item in a non-repeating Item Group for cells that return CQL NULL. This function is only available for use in the projection.

Syntax

FILL(DATAITEM)

Example

select @HDR.Subject.Name, @HDR.Site.Name,
       @Form.SeqNbr, @ItemGroup.SeqNbr,
       CMTRT, fill(CMTRT) as fill_CMTRT, CMSTDAT, CMONGO
from CM
where (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v'))

In this example, the CM form contains a repeating Item Group and a non-repeating Item Group. The CMTRT item is in the non-repeating group, and so it returns -- (for NULL) in the rows for the repeating Item Group instances. This example returns the value for the CMTRT item for the null cells for that column in the fill_CMTRT column.

FormName, FormSeqNbr, ItemGroupName, ItemGroupSeqNbr

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

Syntax

FormName(Item), FormSeqNbr(Item), ItemGroupName(Item), ItemGroupSeqNbr(Item)

Example

SELECT FormName(ae_term), FormSeqNbr(ae_term), ItemGroupName(ae_term), ItemGroupSeqNbr(ae_term)
FROM adverse_event

Labs

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

SPECIMENTYPE

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

Syntax

SpecimenType(LBTEST)

Example

SELECT LBTEST, SpecimenType(LBTEST) FROM edc.baseline_labs

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

TESTINGMETHOD

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

Syntax

TestingMethod(LBTEST)

Example

SELECT LBTEST, TestingMethod(LBTEST) FROM edc.baseline_labs

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

LOINCCODE

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

Syntax

LOINCCode(LBTEST)

Example

SELECT LBTEST, LOINCCode(LBTEST) FROM edc.baseline_labs

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

ANALYTECODE

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

Syntax

AnalyteCode(LBTEST)

Example

SELECT LBTEST, AnalyteCode(LBTEST) FROM edc.baseline_labs

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

LABLOCTITLE

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

Syntax

LabLocTitle(LBLOC)

Example

SELECT LBLOC, LabLocTitle(LBLOC) FROM edc.baseline_labs

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

OVERRIDEREASON

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

Syntax

OverrideReason(LBOVRDNRLO), OverrideReason(LBOVRDNHRI)

Example

SELECT OverrideReason (LBOVRDNRLO), OverrideReason(LBOVRDNRHI) FROM edc.baseline_labs

Coding

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

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

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

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

Reference Objects

The following reference object functions are available:

KEYMATCH

Returns true if there is a match between the related_item and the related_key.

KEYMATCH leverages the related_item and related_key from the manifest file for the Items to match. If the related_item from the Form matches related_key from the reference data, then the value for the keymatch column is true. Otherwise, the value is false.

When you specify the Source for the Form in the first argument of your KEYMATCH function (SourceName.FormName), that SourceName.FormName specified in the first argument must exactly match the SourceName.FormName in the FROM clause.

Syntax

KEYMATCH(form_name, reference_object_name)

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.

CONCAT_WS

Returns arguments together with a separator

Syntax

CONCAT_WS ( separator, DATAITEM1, DATAITEM2)

Example

select @HDR.Subject.Name, @HDR.Event.Name, AETERM, concat_ws ( ' ', 'AE ID ', @Form.SeqNbr, ' . ', @ItemGroup.SeqNbr )
from AE

GROUP_CONCAT

Return a concatenated string of non-NULL values from a group.

Syntax

GROUP_CONCAT ( DATAITEM)

Example

select @HDR.Subject.Name, @HDR.Event.Name, @Form.SeqNbr, group_concat(@ItemGroup.SeqNbr) 
from `EDC`.`AE`
where @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')
group by @HDR.Subject.Name, @Form.SeqNbr

COALESCE

Return the first non-NULL argument

Syntax

COALESCE (DATAITEM1, DATAITEM2, ...)

Example

select @HDR.Subject.Name, MHTERM, MHSTDAT, MHONGO, coalesce(MHTRT_1, MHTRT_2) 
from MHTERM

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

MID

Return a substring starting from the specified position

Syntax

MID ( DATAITEM, start index, length )

Example

select @HDR.Subject.Name, AETOXGR, MID(AETOXGR, 7, 1) as `Grade Number` 
from `EDC`.`AE`

REGEXP

Whether string matches regular expression

Syntax

REGEXP 'regular expression pattern'

Example

select @HDR.Subject.Name, SAE_TERM
from `EDC`.`AE`
where SAE_TERM regexp  ',|\\[|\\]|{|}|\\(|\\)'

REPLACE

Replace occurrences of a specified string

Syntax

REPLACE ( DATAITEM , 'substring' , 'new_string')

Example

select @HDR.Subject.Name, AETOXGR, replace(AETOXGR, 'GRADE', 'G')
from `EDC`.`AE`

SUBSTR

Return the substring as specified

Syntax

SUBSTR (DATAITEM, start index, length)

Example

select @HDR.Subject.Name, AETOXGR, SUBSTR(AETOXGR, 7, 1) as `Grade Number` 
from `EDC`.`AE`

Date & Time

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

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

The following functions are available for dates and times:

ADDDATE

Adds a date/time interval (resulting in a later date) from a given date or Date-type DATAITEM and returns the date/time

You can provide either a number of days or a number value and a unit with INTERVAL.

Syntax

ADDDATE ('date', days)

or

ADDDATE('date', INTERVAL value unit)

CQL supports the following interval units:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Example

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

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

SUBDATE

Subtracts a date/time interval (resulting in an earlier date) from a given date or Date-type DATAITEM and returns the date/time.

You can provide either a number of days or a number value and a unit with INTERVAL.

Syntax

SUBDATE ('date', days)

or

SUBDATE('date', INTERVAL value unit)

CQL supports the following interval units:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Example

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

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

TIMESTAMPDIFF

Subtract an interval from a datetime expression

Syntax

TIMESTAMPDIFF(month, date, date) 

Example

select @HDR.Subject.Name, AESTDT, AEENDDT, timestampdiff(day, AESTDT, AEENDDT)
from `EDC`.`AE`

DATEDIFF

Returns the difference between two dates in days.

Syntax

DATEDIFF('date', 'date')

Example

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

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

DATE_FORMAT

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

Syntax

DATE_FORMAT(<date>,'<format>')

Example

SELECT DATE_FORMAT(@HDR.Event.Date, '%Y-%m-%d')

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

SITENORMALIZEDDATE

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

Syntax

SiteNormalizedDate(date)

Example

SiteNormalizedDate(edc.ae_start_datetime)

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

RAWDATE

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

Syntax

RawDate(<date>)

Example

SELECT RawDate(subject_dob) from EDC.demographics

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

SDTMDATEFORMAT

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

Syntax

SDTMDateFormat(<date>)

Example

SELECT SDTMDateFormat(mh.condition_start_date) from EDC.medical_history

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

STR_TO_DATE

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

Syntax

SELECT STR_TO_DATE('<string>', '<format>')

Example

SELECT STR_TO_DATE('January 01, 2021', '%M %d, %Y')

This query returns Jan 1, 2021 as a date.

CURDATE

Returns the current date as YYYY-MM-DD.

Example

SELECT CURDATE()
FROM Demographics

This query returns the current date.

NOW

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

Example

SELECT NOW()
FROM Demographics

This query returns the current date and time.

YEAR

Returns the year from a given date.

Syntax

YEAR('date')

Example

SELECT YEAR('2019-12-12')
FROM Demographics

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

MONTH

Returns the month from a given date.

Syntax

MONTH('date')

Example

SELECT MONTH('2019-12-12')
FROM Demographics

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

WEEK

Returns week of the year from a given date.

Syntax

WEEK('date')

Example

SELECT WEEK('2019-12-12')
FROM Demographics

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

DAY

Returns the day from a given date.

Syntax

DAY('date')

Example

SELECT DAY('2019-12-12')
FROM Demographics

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

HOUR

Returns the hour, given a time.

Syntax

HOUR('time')

Example

SELECT HOUR('10:05:03')
FROM Demographics

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

MINUTE

Returns the minute, given a time.

Syntax

MINUTE('time')

Example

SELECT MINUTE('10:05:03')
FROM Demographics

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

SECOND

Returns the second, given a time.

Syntax

SECOND('time')

Example

SELECT SECOND('10:05:03')
FROM Demographics

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

LAST_DAY

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

Syntax

LAST_DAY('date'), LAST_DAY(DATAITEM)

Example

SELECT LAST_DAY(@HDR.Event.Date)

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

Unknown Dates

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

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

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

Unknown

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

Syntax

Unknown(Date)

Example

Unknown(edc.mh_condition_start_date)

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

UnknownImpute

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

Syntax

UnknownImpute(Item, 'day impute type', 'month impute type', 'time impute type')

Impute Types:

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

• Day:
  • FIRST DAY: First day of the month.
  • LAST DAY: Last day of the month.
  • MID DAY: 15th of the month.
• Month:
  • FIRST MONTH: January (1)
  • LAST MONTH: December (12)
    • MID MONTH: June (6)
• Time:
  • FIRST HOUR: 00:00 24hr
  • LAST HOUR: 23:59 24hr
  • MID HOUR: 12:00 24hr

Example

UnknownImpute(edc.ae_start_datetime, 'FIRST DAY', 'FIRST MONTH', 'FIRST HOUR')

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

Date & Time Specifiers

The following date and time specifiers are available in CQL:

Raw Format Dates

SDTM Format Dates

Other

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

CAST

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

Syntax

CAST(item AS data_type) 
CAST (expression AS data_type

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

• Binary
• CHAR
• DATE
• DATETIME
• TIME
• DECIMAL
• SIGNED
• UNSIGNED

Example

SELECT CAST(Exclusion_Criteria.Hypersensitivity_Constituents as CHAR)
FROM Demographics

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

FIELD

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

Syntax

FIELD(DATAITEM, '<string1>', '<string2>','<stringN>')

Example

SELECT @HDR.Subject, Route, Dosage_Amount
FROM Dosing
ORDER BY FIELD(Route, 'Oral', 'Nasal', 'Topical')

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

UNION

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

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

Syntax

SELECT ItemGroup.Item, ItemGroup.Item FROM Form1
UNION
SELECT ItemGroup.Item, ItemGroup.Item FROM Form2

Example

SELECT test.date_collected, test.rbc, test.wbc FROM LocalLabs.Hematology
UNION
SELECT chem.date_collected, chem.total_protein, chem.urea_nitrogen FROM CentralLabs.Chemistry

UNPIVOT

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

Syntax

SELECT up.@HDR.Subject.Name, up_items
FROM (
    SELECT @HDR.Subject.Name, itemA, itemB, itemC, itemD) up
UNPIVOT (
    up_items
    FOR items in (itemA, itemB, itemC, itemD)
) as unpivot_table

Example: Unpivot

SELECT up.@HDR.Subject.Name up_items
FROM (
	SELECT @HDR.Subject.Name, body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_ FROM Vitals) up
UNPIVOT (
up_items 
FOR items in (body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_)
) as unpivot_table

Example: Multiple Unpivot Sections

SELECT @HDR.Subject.Name, @HDR.Event, Body_Temperature, vsResult, vsTest, VSSTAT, HIDe 
FROM ( SELECT @HDR.Subject.Name, Body_Temperature, Height, Weight, ILB(Body_Temperature), ILB(Height), ILB(Weight) From EDC.Vitals) main
UNPIVOT (vsResult FOR vsTest in (Body_Temperature, Height, Weight)) as result
UNPIVOT (VSSTAT FOR HIDe in (ILB(Body_Temperature), ILB(Height), ILB(Weight))) as ilb

Example: UnPivot on Multiple Forms:

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

Unsupported Use Cases:

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

• UnPivot within a subquery
• UnPivot within Union
• Referencing @Form or @ItemGroup properties within UnPivot

UnPivot within a Subquery:

SELECT @HDR.Subject.Name, creation_criteria.subject_initials, creation_criteria.subject_dob, up.*
FROM demographics.creation_criteria
(SELECT @HDR.Subject.Name, up_item.vs_test, up_item.vs_result
FROM (SELECT body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_ FROM Vitals) as main
UNPIVOT (vs_result FOR vs_test IN (body_temperature, height, weight, systolic_blood_pressure__mmhg_, diastolic_blood_pressure__mmhg_, pulse__bpm_)) AS up_item) AS up
WHERE d.@HDR.Subject.Name = up.@HDR.Subject.Name

UnPivot within Union:

SELECT @HDR.Subject.Name, @HDR.Event.Name, up_item.*
FROM (SELECT body_temperature, height, weight, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_ FROM vitals) AS main
UNPIVOT (vs_result FOR vs_test IN (vody_temperature, systolic_blood_pressure__mmHg_, diastolic_blood_pressure__mmHg_, pulse__bpm_)) AS up_item
UNION ALL
SELECT @HDR.Subject.Name, @HDR.Event.Name, up_item.*
FROM (SELECT WBC, RBC, HEMOGLOBIN, HEMATOCRIT, PLATELET, NEUTROPHILS FROM hematology) AS main
UNPIVOT (vs_result FOR vs_test IN (WBC, RBC, HEMOGLOBIN, HEMATOCRIT, PLATELET, NEUTROPHILS)) AS up_item;

Other Available Functions

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

CQL Errors

In the unlikely event you receive generic CQL run-time errors (error codes 0001 through 0010), contact Veeva Support.