临床查询语言（CQL）引用

可以使用临床查询语言（CQL）访问、检索 Veeva 临床数据库（CDB）中的临床数据并与之交互。本指南详细介绍了如何最佳利用 CQL，并概述了其语法、结构、子句和运算符。虽然 CQL 查询与结构化查询语言（SQL）共享许多语法元素，但 CQL 语句允许使用精简的、以临床为中心的语法专门针对 EDC 数据执行查询。

在 CDB Workbench 中查询数据时，它会传递一个 CQL 语句，该语句指定要在 FROM 子句中查询的对象、要在 SELECT 子句中检索的字段以及要应用的任何可选筛选（在 WHERE 子句中）。

以下示例查询返回 Cholecap 2019 1 期研究中人口统计信息和入选标准表单的所有数据条目。

SELECT * FROM F.Demographics, F.Inclusion_Criteria 

CQL 的临床上下文

CQL 理解常见的临床数据模式和关系。这种“上下文”提供了一种比通用查询语言（如 SQL）更简单的语言语法。CQL 理解以下预定义的对象和关系：

可查询数据

在利用 CQL 创建查询时，了解哪些数据是可查询的以及这些数据的结构非常重要。可以使用 DESCRIBE 和 SHOW 关键字来返回 CQL 的可查询数据列表。

描述（Describe）

可以使用 DESCRIBE 关键字来创建一个查询，以获取关于提要的详细信息。

CQL 支持以下数据元素的 DESCRIBE：

• HEADER
• FORM
• ITEMGROUP
• ITEM
• REFERENCE_OBJECT

调用 DESCRIBE 时，CQL 会返回表格或对象中的每个字段。

DESCRIBE 表单

对表单调用 DESCRIBE 将返回表单中包含的所有条目（包括名称和数据类型）的列表。

DESCRIBE Form VITALS 

此查询会返回 Cholecap 2019 - 1 期研究中生命体征表单上所有条目的列表。

显示（Show）

还可以使用 SHOW 关键字来创建一个查询，获取可能值的列表。可以将 SHOW 应用于事件组、事件、表单和代码列表条目。还可以使用 SHOW 来返回当前 Vault 中的研究的列表。

显示研究

可以使用 SHOW 来返回当前 Vault 中的研究的列表：

SHOW STUDIES 

显示事件组

可以使用 SHOW 来返回当前研究中的事件组列表：

SHOW EVENTGROUPS 

显示事件

可以使用 SHOW 来返回当前研究中的事件列表：

SHOW EVENTS 

显示表单

可以使用 SHOW 来返回当前研究中的表单列表：

SHOW FORMS 

显示代码列表条目

可以使用 SHOW 来返回给定代码列表中的代码列表条目列表。请注意，必须提供代码列表类型数据条目的名称。

SHOW CODELIST <DATAITEM> 

显示视图

可以使用 SHOW 来返回研究中的视图列表。

SHOW Views 

显示参考

可以使用 SHOW 来返回研究中的参考对象列表。

SHOW Reference 

系统列表

可以使用 CALL 通过 CQL 访问系统列表。系统列表是为原始类型导出生成的列表，其中包含有关研究和所收集数据的上下文信息。

• 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

视图

可以使用 SHOW VIEWS 通过 CQL 访问视图。这将返回当前研究中所有现有视图的列表。视图是专门的列表。在编写用于其他视图的 CQL 时，还可以引用选择中的视图。为此，请使用视图的名称（标题）作为源。

定义视图时的 CQL 限制

定义视图时，不支持以下 CQL 功能：

• select *
• UnPivot()
• 重复的列别名
• 投影中除 @Form.SeqNbr 之外的其他 @Form 属性
• 投影中除 @ItemGroup.SeqNbr 之外的其他 @ItemGroup 属性

引用视图时的 CQL 限制

在自定义列表中引用视图时，存在以下限制：

• 不支持在另一个视图内部引用视图。
• 投影或过滤器（WHERE 子句）中不支持包含视图的 @Form 和 @ItemGroup 属性。
• 如果使用 On Subject ALIGN，则视图不得使用 UNALIGN。
• 在将视图与另一个表单或视图连接时，如果使用 On Subject，视图必须使用 On Subject。

视图中的 On Subject 连接

在将视图与另一个表单或视图连接时，视图支持使用 On Subject ALIGN 和 On Subject UNALIGN。

要在视图和表单之间执行 on subject 连接，视图的视图定义必须包含 On Subject。否则，CQL 将返回以下错误提示：

“视图只能按其定义的方式进行连接。请确保视图定义和视图引用中的 On Subject 对齐方式相同。“

使用 On Subject ALIGN 的视图定义

以下视图定义会检索受试者在发生不良事件时所使用的药物。这个视图称为”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 

现在，这个视图可以与另一个表单、生命体征连接，以获取这些受试者的身高、体重或 BMI 等生命体征。可以使用 On Subject ALIGN 和 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')) 

使用 On Subject UNALIGN 的视图定义

以下视图定义会检索受试者在发生不良事件时所使用的药物。这个视图称为”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 

定义中包含 On Subject UNALIGN 的视图只能与使用 On Subject UNALIGN 的另一个表单进行连接，如下所示：

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

参考对象

要检索研究中的所有参考对象列表，请使用 SHOW 关键字：

SHOW Reference 

然后，可以使用 DESCRIBE 关键字来检索给定参考对象上的条目列表：

DESCRIBE Reference <Reference_Object_Name> 

使用 ReferenceRelations 关键字检索以下属性的键值对表：reference_object_name,related_form, related_item, and related_key。

ReferenceRelations <Reference_Object_Name> 

对于文本类型的参考对象条目，CQL 支持以下函数：

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

对于日期类型的参考对象条目，CQL 支持以下函数：

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

参考对象的 CQL 限制

使用参考对象时，CQL 限制适用。

以下函数不能与参考数据一起使用：

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

查询语法和结构&

CQL 查询返回一个元组：一个具有已定义列顺序的数据表。为了返回一个元组，CQL 查询必须指定以下内容：

• 投影：要检索的特定数据元素。
• 选择：包含投影中数据元素的特定数据表。
• 过滤器（可选）：要返回的数据元素的匹配标准（这些不必包含在元组中，可以通过研究中的隐式关系进行引用）。
• 排序（可选）：元组中行的返回顺序。

投影

CQL 查询的投影指定要检索的数据元素以及这些元素在结果集中的顺序。可以使用 DATAITEM 名称显式指定字段列表，也可以使用通配符快捷方式。

使用 SELECT 语句描述投影。

通配符

可以在 SELECT 语句中使用通配符星号，以布局顺序返回 FROM 子句中指定 FROM 上下文中关联 DATAITEMS 的所有列。

SELECT * FROM Demographics 

上下文限定星号

可以使用上下文限定星号来返回限定符上下文中的所有 DATAITEMS，而不是使用通配符星号。

在当前版本中，仅支持 FORM 和 ITEMGROUP 限定符。

SELECT <Qualifier>.* FROM <Form_Name> SELECT Creation_Criteria.* FROM Demographics 

此示例查询会返回 Cholecap 2019 1 期研究中人口统计信息表单上创建标准条目组中的所有 DATAITEMS。

数据条目

或者，可以指定要检索的特定 DATAITEMS。可以在投影中选择存在于指定 FORM 中的任何 DATAITEM。如果只指定一个 FORM，则可以直接引用这些列。如果指定了多个 FORM，则必须使用表单名称或别名限定每个 DATAITEM。

SELECT <Qualifier>.<DataItem> FROM <Form_Name> SELECT Demographics.Subject_Date_of_Birth, Enrollment.Randomization_Number FROM Demographics, Enrollment 

此示例查询会返回人口统计信息表单中出生日期条目和入组表单中随机化编号条目的所有值。

数据函数

还可以指定可以作为结果集特定列返回的函数。函数可以使用 DATAITEMS 作为参数，以及上述许多上下文。

可以使用函数访问 DATAITEM 的所有属性，但名称和 ID 除外。

有关函数、接受的参数和定义的完整列表，请参阅下文。

SELECT STATUS(Demographics.Subject_Date_of_Birth) FROM Demographics 

此示例查询会返回 Cholecap 2019 1 期研究中人口统计信息表单上的出生日期条目的状态。

Distinct

可以使用 DISTINCT 仅返回给定列或一组列中值的唯一实例。

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

此示例查询会返回 Cholecap 2019 1 期研究中给药表单上剂量数量条目的所有唯一值。

别名（AS）

可以使用 AS 为查询中的表单和数据条目定义别名。当表单或数据条目的名称较长且想要多次引用时，别名非常有用。

可以为投影中的特定列提供别名。如果为数据条目提供别名，Workbench 会在列表中用该别名重命名该条目的列。如果数据条目的名称较长，数据条目的别名特别有用，如下例所示。在核心列表中查看结果时，使用较短的数据条目别名有助于限制水平滚动的需要。

还可以为表单提供别名。如果想在一个查询中多次引用表单，这将非常有用。设置别名后，只需使用别名，而不必使用表单名称来引用该表单。

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 

此示例查询会返回人口统计信息表单中出生日期条目和入组表单中随机化编号条目的所有值。

操作投影元素

除了 DATAITEMS 之外，还可以查询可能对查询结果有用的操作数据。其中最强大的操作元素是标题。标题是与请求的表单和数据条目相关的最重要研究元数据的摘要。还可以请求有关研究、研究中心、受试者、事件组和事件的信息。

可以在任何投影中使用 @HDR 变量符号返回标题信息。可以使用通配符查询 @HDR 信息，以检索所有可查询的元数据，或查询与表单相关的特定标题信息。

SELECT @HDR, <Qualifier>.<DataItem> FROM <Form_Name> SELECT @HDR.SITE, * FROM Dosing 

上述查询会返回研究中心的摘要信息。

标题摘要

可以使用一组预定义的元素和列顺序在给定的上下文中创建特定的摘要。以下标题上下文可用：

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

如果单独使用 @HDR 变量和上下文，例如 @HDR.Study，CQL 仅会返回一组有限的可用属性。

标题详细信息属性

还可以针对标题的特定元素进行操作，以便在选择中返回或在筛选子句中使用。使用点符号将属性名称附加到 @HDR 上下文，以检索该属性。例如，使用 @HDR.Study.Phase 返回研究的阶段，或使用 @HDR.Event.Date 返回事件的事件日期。

以下是每个 @HDR 上下文元素可用的属性，使用点符号。

表单和条目组属性&

可以在选择或筛选子句中针对表单和条目组的属性进行操作。使用点符号将属性名称附加到 @Form 或 @ItemGroup 上下文，以检索该属性。例如，使用 @Form.CreatedDate 返回表单的创建日期。

表单链接条目

可以使用 @FormLink 表示法针对在条目到表单链接中使用的条目进行操作。包含表单链接条目的表单是链接表单。表单链接条目链接到链接表单。要使用 @FormLink 表示法，必须提供链接表单上表单链接条目的名称，以及希望在链接表单上针对的条目。

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

如果链接表单中的条目在多个条目组中使用，还必须提供条目组的名称。

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

如果链接表单在多个条目组中使用相同的表单链接条目，则必须指定条目组表单链接的名称。

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

以下示例假设 AE 表单上有 2 个表单链接：ae_to_cm 和 ae_to_mh。可以使用以下 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') 

FormLink ae_to_cm 位于 ae_to_cm 条目和 CM 表单之间。可以使用 @FormLink.ae_to_cm.CMONGO 访问链接表单 CM 中的 CMONGO 条目。

FormLink ae_to_mh 位于 ae_to_mh 条目和 MH 表单之间。可以使用 @FormLink.ae_to_mh.MHSTDAT 访问链接表单 MH 中的 MHSTDAT 条目。

可以在 WHERE 子句、Order By 子句和 Group By 子句中使用通过 @FormLink 表示法表示的条目。

以下 CQL 功能支持表单链接条目：

• UNION 和 UNION ALL
• On Subject ALIGN 和 UNALIGN
• 子查询
• UNPIVOT

查询属性

可以在选择或筛选子句中针对查询的属性进行操作。使用点符号将属性名称附加到 @QRY 或 @QRYMSG，以检索该属性。例如，使用 @QRY.ID 检索查询的 ID。

@QRY

使用 @QRY 可查询查询对象的以下属性：

@QRYMSG

使用 @QRYMSG 可查询查询消息对象的以下属性：

选择

选择（FROM）是一种机制，通过该机制可以指定投影所操作的表单的名称。在使用通配符星号时，选择还指定投影的表单顺序。

在 CQL 中，FROM 始终指代从中选择数据的表单上下文。不能在 FROM 子句中使用任何其他上下文。

由于可通过 @HDR 变量访问标题，因此，如果不需要 FORM 和 DATAITEMS，也可以单独选择标题的任何元素。可以使用这些类型的选择来创建仅按受试者列出操作数据的结果。在投影仅包含 @HDR 时，可以在不提供 FROM 的情况下筛选此选择。但是，要使用别名和子查询，必须包含 FROM 子句。

On Subject 连接

默认情况下，CQL 对受试者和事件进行连接。因此，如果从不同事件的两个表单中进行选择，CQL 将为每个表单返回一行，而不是为每名受试者返回一行。在这种情况下，可以通过仅 On Subject 连接，为每名受试者返回一行，其中包含两个表单的数据。

要进行 On Subject 连接，将 on subject 附加到选择中的表单标识符的列表。可以使用任意数量的标识符，但 on subject 适用于所有指定的表单。

在与 on subject 一起使用时，CQL 会为任何 @HDR.EventGroup 或 @HDR.Event 属性返回 null，因为 on subject 可以返回来自多个事件组或事件的数据）。如果需要检索这些属性，可以通过选择每个表单的表单别名来检索。

以下示例查询会为每名受试者返回一行，其中包含 @HDR.Subject 属性、受试者姓名首字母、受试者出生日期、身高、体重、脉搏、收缩压、舒张压和体温。如果示例查询未使用按受试者连接，CQL 会为人口统计信息表单中的条目返回一行，为生命体征表单中的条目返回第二行。

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 

对齐和未对齐修饰符&

在对重复的表单或条目组使用 on subject 时，可以选择返回对齐序列的数据（对齐）或即使序列未对齐时也返回数据（未对齐）。例如，在显示来自人口统计信息表单和重复不良事件表单的信息时，CQL 会在为受试者返回的所有不良事件表单实例的行中返回人口统计信息表单中的数据。这种行为被称为“未对齐”，因为即使序列未对齐，它也会返回数据。UNALIGN 是默认行为，因此在 CQL 语句中显式包含此修饰符是可选的。在此示例中，“对齐”行为将仅返回人口统计信息表单中为受试者的所有不良事件表单实例返回的第一行数据。要使用对齐行为，请包括 ALIGN 修饰符。

高级连接

CQL 还支持高级（定向）连接。使用高级连接，无需先连接到 @HDR 即可连接表单。

LEFT JOIN：从第一个（左侧）表中获取所的有记录，以及来自第二个（右侧）表中任何匹配的数据。RIGHT JOIN：从第二个（右侧）表中获取的所有记录，以及来自第一个（左侧）表中任何匹配的数据。INNER JOIN：仅两个表中找到匹配的记录

在当前版本中，CQL 不支持外连接。

无法使用高级连接将参考数据与其他数据连接。

示例：表单之间的左联接

在以下 CQL 语句中，根据表单定义属性 @Form.Subject，AE 和 CM 表单之间存在一个 LEFT JOIN。请注意，受试者.姓名是表单定义的一部分，而不是来自@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' 

不需要连接到 @HDR 即可有效，因为连接条件是基于 @Form 属性（@Form.Subject.Name），并且投影中不包含任何 @HDR 属性。

示例：表单和 @HDR 之间的左联接&

以下 CQL 查询需要与 @HDR 连接，因为投影具有 @HDR 属性。以下查询基于 Event.ID 在 @HDR 和 AE 之间建立了一个左连接 - 然后，这个左连接的输出再基于 Event.ID 和 Subject.ID 与 CM 进行左连接。

在以下 CQL 语句中，投影包含了 @HDR 属性，因此需要与 @HDR 进行连接。这是一个基于 @Form.Event.ID的 @HDR 和 AE 表单之间的 LEFT JOIN。该连接的输出随后又基于 @Form.Event.ID 和 @Form.Subject.ID，与 CM 表单建立了一个 LEFT JOIN。

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 

子查询

CQL 支持子查询。

使用 IN 的子查询

可以定义一个子查询，该子查询返回一个值列表，可以在筛选器的 IN 运算符中引用这些值。

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

此查询返回所有女性受试者的随机化表单中的所有 DATAITEMS。

子查询连接

还可以定义两个 SELECT 语句，使用子查询（而不是典型的 JOIN 语法）将其连接在一起。使用逗号（,）表示联接，将第二个子查询左外连接到第一个子查询。子选择可以使用别名，但这不是必需的。这些连接隐式包含 EventId 和 SubjectId。可以在子查询后使用筛选（WHERE）子句进行额外的筛选。

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 

函数中的子查询

可以在函数中使用子查询，包括 @HDR、@Form 标题和 DATAITEMS。

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

相关子查询

CQL 允许使用相关子查询来筛选结果。

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

筛选

可以使用筛选器来减少通过投影和选择提供的初始 CQL 结果的值。这是可选的。CQL 中的主要过滤机制是 WHERE 子句。

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

比较函数和运算符&

以下函数和运算符可在 WHERE 子句中使用：

顺序

可以使用 ORDER BY 子句自定义结果的顺序。

例如：

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

备注

如果需要，还可以在 CQL 语句中包含备注。要包含备注，请输入两个短划线和一个空格（-- ），后跟备注。

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

数据汇总

CQL 可以对查询结果执行基本的数据汇总。可以在聚合函数中引用标题的任何元素或任何 DATAITEM。聚合函数接受通配符、@ 变量和特定 DATAITEMS 作为参数。

聚合函数

由于聚合函数不支持与 WHERE 一起使用，因此可以使用 HAVING 关键字来替代。要使用 HAVING，CQL 投影必须至少包含一个聚合函数。

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

对结果进行分组

CQL 支持 GROUP BY 语法，只要在投影中包含至少一个聚合函数即可。

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

结果结构

CQL 结果根据在投影中提供的元素按特定顺序返回。CQL 包括三种快速识别应包含在投影中的内容的方法：标题、通配符和限定通配符。可以在查询中指定应返回的其他列。

根据投影中包含的 DATAITEMS，执行数据的排序顺序也会有所不同。

表单和数据条目结果&

与 SELECT @HDR 表示法一样，其余的 FORM 和 DATAITEMS 可以使用 SELECT * 或SELECT ItemGroup.* 返回。使用这些表示法时，结果集有一个默认的列顺序，并且此部分中的行在遵循应用于标题（如果包含在投影中）的排序顺序之后，将按特定排序顺序返回。

如果在查询中不使用通配符表示法，则必须在投影中指定希望返回的数据列。结果的顺序将符合以下列出的排序选项的层级结构，具体取决于投影中包含的元素。

请注意，任何包含在排序层级结构中的对象都将存在，即使该特定列不存在，排序层级规则仍然适用。

SELECT * 默认列顺序

列的布局是从下面列出的 FORM 属性开始，然后按照表单的布局顺序进行布局。

• 表单名称
• 表单序列号（仅在表单重复时）
• 项组
• 序列号（仅在条目组重复时）
• 条目 1
• 条目 2
• 条目 N

SELECT ItemGroup.* 默认列顺序

列的布局是从下面列出的 FORM 和条目组属性开始，然后按照条目组的布局顺序进行布局。

• 表单名称
• 表单序列号（仅在表单重复时）
• 项组
• 序列号（仅在条目组重复时）
• 条目 1
• 条目 2
• 条目 N

列标题

CQL 使用特定模式为每列命名：

• @HDR列：Object.Property，例如，对于 @HDR.Site.Name，列名为“Site.Name”
• 数据条目：条目定义的名称
• 别名：对于任何具有别名的数据，CQL 将使用别名作为列标题。

由于表单和条目组可以重复，它们遵循与标题结果相同的 Object.Property 表示法，例如：Form.Name、ItemGroup.SeqNbr 等。

宽格式和紧凑格式&

CQL 始终在结果集中返回带有 @Form 和 @ItemGroup 信息的数据条目，这被称为表单标题。这些信息对于理解给定数据条目是在哪个表单和条目组中收集的非常有用。除了表单标题之外，还可以选择以宽格式或紧凑格式排列数据条目。默认情况下，CQL 以宽格式返回结果。

宽格式是指在查询的上下文中，每个数据条目都以独立的列形式返回，适用于每一个收集的表单或条目组。

紧凑格式是指数据条目的结果以单一列的形式呈现，而不论其来源于哪个表单或条目组。这种格式在比较条目值时非常实用。

要以紧凑格式返回结果，请在 SELECT 语句中指定 COMPACT：

SELECT COMPACT @HDR, Creation_Criteria.* FROM Demographics 

该查询以紧凑格式返回人口统计信息表单中的创建标准条目组的 @HDR 摘要和 DATAITEMS。

示例：多个表单重用同一个条目

在此示例中，一项研究有两个表单，即人口统计信息和知情同意，两个表单都使用出生日期条目。

宽格式

紧凑格式

示例：重复表单

在此示例中，一项研究有重复的不良事件表单。

宽格式

紧凑格式

示例：重复条目组

在此示例中，一项研究有一个体格检查表单，其中有一个按身体部位进行检查的重复的条目组。

宽格式

紧凑格式

日志表单事件

EDC 中的某些事件没有与之关联的事件日期。这些事件被称为“日志”事件，而这些事件中的 FORM 则称为“日志表单”。常见的日志表单示例包括不良事件和合并用药表单。这些日志表单中的信息独立于计划事件进行收集。

当日志表单包含在投影中时，CQL 会将这些表单分组到一个日志事件下。日志事件始终列在结果集中的所有其他事件组之后。

函数

CQL 支持多种函数，以提供有关给定 DATAITEM 或 FORM 的额外信息。可以在 CQL 投影、筛选器和 Order By 子句中包含任一函数。CQL 还包括一组专门用于查询临床数据的函数。

• 临床函数
• 编码函数
• 控制流函数
• 字符串函数
• 日期和时间函数&
• 其他函数

临床

以下临床函数可用：

DESCRIPTION

返回指定数据条目的条目定义中提供的描述。

语法

DESCRIPTION(DATAITEM) 

示例

SELECT DESCRIPTION(Randomization.Randomization_Number) FROM Enrollment 

此查询返回入组表单中随机化编号条目的描述值。

DMR

如果 DATAITEM 已经过数据管理审查（DMR），则返回真（true），否则返回假（false）。

语法

DMR(DATAITEM) 

示例

SELECT @HDR.Subject.Name, @HDR.Site.Name, @HDR.Event.Name, @HDR.Event.Date, AESTDAT, DMR(AESTDAT) as `Is AE Start Date DMR` FROM AE where @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v') 

FROZEN

根据给定数据条目当前是否被冻结返回真或假。如果该值为 NULL，则从其父项（条目组和表单）继承。

语法

FROZEN(DATAITEM) 

示例

SELECT FROZEN(Creation_Criteria.Initials) FROM Demographics 

如果人口统计信息表单上的姓名首字母条目被冻结，则此查询返回 true。如果姓名首字母条目已解锁，则此查询返回 false。

HELPTEXT

返回指定数据条目的条目定义中提供的帮助文本。

语法

HELPTEXT(DATAITEM) 

示例

SELECT HELPTEXT(Randomization.Randomization_Number) FROM Enrollment 

此查询返回入组表单中随机化编号条目的帮助文本（在 Vault EDC 中创建的条目的帮助内容属性）值。

HINTLABEL

返回指定数据条目的条目定义中提供的提示标签。

语法

SELECT HINTLABEL(Randomization.Randomization_Number) FROM Enrollment 

此查询返回入组表单中随机化编号条目的提示标签（在 Vault EDC 中创建的条目的提示标签属性）值。

ILB

根据给定 DATAITEM 是否标记为故意留空返回真或假。如果该值为 NULL，则从其父表单继承。

语法

ILB(DATAITEM) 

示例

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

对于任何剂量条目没有标记为故意留空的受试者，此查询从给药表单中返回标题摘要（@HDR）和所有数据条目（*）。

ILBREASON

返回与标记为故意留空的 DATAITEM 或 FORM 相关联的更改原因。

语法

ILBREASON(DATAITEM) 

示例

SELECT ILBREASON(Dosage_Amount) FROM Dosing WHERE ILB(Dosage_Amount) = TRUE 

此查询返回给药表单上标记为故意留空的剂量条目的更改原因。此查询筛选结果，以便 CQL 不返回没有被标记为故意留空的剂量条目的行。

LABEL

返回指定 DATAITEM 的条目定义中提供的标签。可以选择指定一个替代的区域设置，以返回本地化标签。默认情况下，LABEL() 返回当前用户区域设置中的标签。在此处查看 Vault CMDS 区域设置值的列表。

语法

LABEL(DATAITEM,['locale']) 

示例

SELECT LABEL(Randomization.Randomization_Number, 'es_PR') FROM Enrollment 

此查询返回入组表单中随机化编号条目的本地化（适用于 es_PR，或 Puerto Rico）条目标签。

CODELABEL

返回为代码列表类数据条目选择的代码列表条目提供的标签。

语法

CODELABEL(DATAITEM) 

示例

SELECT CODELABEL(adverse_event.AEACTN) FROM Adverse_Event 

此查询返回为 AEACTN（对研究药物采取的措施）代码列表条目选择的代码列表条目的标签。

LASTMODIFIEDDATE

返回上次修改 DATAITEM 的日期（对于 Vault EDC 中的数据，这基于条目对象上的最后修改日期字段）。

语法

LASTMODIFIEDDATE(DATAITEM) 

示例

SELECT LASTMODIFIEDDATE(Randomization.Randomization_Number) FROM Enrollment 

返回入组表单中随机化编号条目的上次修改日期。

LOCKED

根据给定 DATAITEM 当前是否已锁定返回真或假。如果该值为 NULL，则从其父项（条目组和表单）继承。

语法

LOCKED(DATAITEM) 

示例

SELECT LOCKED(Creation_Criteria.Initials) FROM Demographics 

如果人口统计信息表单上的姓名首字母条目已锁定，则此查询返回 true。如果姓名首字母条目已解锁，则此查询返回 false。

SDV

如果 DATAITEM 已经过源数据验证（SDV），则返回真，否则返回假。

语法

SDV(DATAITEM) 

示例

SELECT @HDR.Subject.Name, @HDR.Site.Name, @HDR.Event.Name, @HDR.Event.Date, AETERM, SDV(AETERM) as `Is AE Term SDV` FROM AE where @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v') 

SIGNED

根据给定 DATAITEM 当前是否已签名返回真或假。如果该值为 NULL，则从其父项（条目组和表单）继承。

语法

SIGNED(DATAITEM) 

示例

SELECT SIGNED(Creation_Criteria.Initials) FROM Demographics 

如果人口统计信息表单上的姓名首字母条目已签名，则此查询返回 true。如果姓名首字母条目尚未签名，则此查询返回 false。

STATUS

返回 DATAITEM 的状态。还可以使用此函数通过使用点表示法和 @Form 变量来返回表单的状态，而不是使用 STATUS()。请参阅第二个示例。

语法

STATUS(DATAITEM) 

示例

SELECT STATUS(Randomization.Randomization_Number) FROM Enrollment 

此查询返回入组表单中随机化编号条目的状态值。

SELECT @Form.Status FROM Enrollment 

此查询返回入组表单的状态。

UOM

返回 DATAITEM 的计量单位（UOM）

语法

UOM(DATAITEM) 

示例

SELECT UOM(Vitals.Weight) FROM Vitals 

此查询返回生命体征表单中体重条目的 UOM。

UOMTRANSLATED

返回默认单位的度量单位（UOM），而不是用户输入的单位（例如，华氏度而不是摄氏度）

语法

UOMTRANSLATED(Vitals.Temperature) 

示例

SELECT TRANSLATED(Vitals.Temperature) FROM Vitals 

此查询返回“华氏度”，这是体温条目的默认单位。

TRANSLATED

返回默认单位的值，该值从用户输入的单位转换为默认单位

语法

TRANSLATED(DATAITEM) 

示例

SELECT TRANSLATED(Vitals.Temperature) FROM Vitals 

此查询从生命体征表单中的体温条目返回以华氏度（默认单位）为单位的值，即使研究中心用户输入的温度以摄氏度为单位。

FILL

对于返回 CQL NULL 的单元格，从非重复条目组中的条目返回指定的填充值。此函数仅可在投影中使用。

语法

FILL(DATAITEM) 

示例

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

在此示例中，CM 表单包含一个重复条目组和一个非重复条目组。CMTRT 条目位于非重复组中，因此它在重复条目组实例的行中返回 --（表示 NULL）。此示例返回 fill_CMTRT 列中该列 Null 单元格的 CMTRT 条目的值。

FormName, FormSeqNbr, ItemGroupName, ItemGroupSeqNbr

返回数据条目的表单和条目组的名称和序列号。

语法

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

示例

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

实验室

以下函数可与 EDC 的实验室模块中的数据一起使用。

SPECIMENTYPE

返回 LBTEST 列中指定分析物的标本类型。将 LBTEST 条目与此函数一起使用。

语法

SpecimenType(LBTEST) 

示例

SELECT LBTEST, SpecimenType(LBTEST) FROM edc.baseline_labs 

上述示例返回 LBTEST 条目和该检测的标本类型。

TESTINGMETHOD

返回 LBTEST 列中指定分析物的检测方法。将 LBTEST 条目与此函数一起使用。

语法

TestingMethod(LBTEST) 

示例

SELECT LBTEST, TestingMethod(LBTEST) FROM edc.baseline_labs 

上述示例返回 LBTEST 条目和该检测的检测方法。

LOINCCODE

返回 LBTEST 列中指定分析物的 LOINC 编码。将 LBTEST 条目与此函数一起使用。

语法

LOINCCode(LBTEST) 

示例

SELECT LBTEST, LOINCCode(LBTEST) FROM edc.baseline_labs 

上述示例返回 LBTEST 条目和该检测的 LOINC 编码。

ANALYTECODE

返回 LBTEST 列中指定分析物的分析物代码。将 LBTEST 条目与此函数一起使用。

语法

AnalyteCode(LBTEST) 

示例

SELECT LBTEST, AnalyteCode(LBTEST) FROM edc.baseline_labs 

上述示例返回 LBTEST 条目和该检测的分析物代码。

LABLOCTITLE

返回实验室位置的实验室标题。LBLOC 仅返回实验室 ID。将 LBLOC 条目与此函数一起使用。

语法

LabLocTitle(LBLOC) 

示例

SELECT LBLOC, LabLocTitle(LBLOC) FROM edc.baseline_labs 

上述示例返回在基线实验室表单中选择的实验室 ID（LBLOC）和实验室位置的实验室位置标题。

OVERRIDEREASON

返回研究中心用户在输入正常覆盖时提供的覆盖原因。将 LBOVRDNRLO 和 LBOVRDNRHI 条目与此函数一起使用。

语法

OverrideReason(LBOVRDNRLO), OverrideReason(LBOVRDNHRI) 

示例

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

编码

以下函数允许检索有关已分配的已编码术语和编码表单状态的信息。Vault Coder 支持使用 MedDRA 和 WHODrug（B3 和 C3）词典进行编码。

CODEDTERM

如果存在已编码术语（表单的编码请求处于已编码或已自动编码状态），则返回数据条目的已编码术语。CQL 默认返回 MedDRA 词典的首选语和 WHODrug 词典的首选名称。可以指定一个术语级别以返回另一个级别，例如 HLT 或 LLT。

语法

CODEDTERM(Item, 'TermLevel') 

示例

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 

术语级别

CODEDCODE

如果存在编码代码（表单的编码请求处于已编码或已自动编码状态），则返回数据条目的编码代码。CQL 默认返回 MedDRA 词典的首选语编码和 WHODrug 词典的首选编码。可以指定一个编码等级以返回另一个等级。

语法

CODEDCODE(Item, 'CodeLevel') 

示例

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 

编码等级

CODEDDICTREL

返回分配给表单的已编码词典版本。

语法

CODEDDICTREL(Item) 

示例

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

CODEDSTATUS

返回条目（编码请求）的编码状态。

语法

CODEDSTATUS(item) 

示例

SELECT CODEDSTATUS(ae_term.ae) FROM adverse_event 

CODEDBY

返回条目的编码者（对编码请求进行编码的用户的用户 ID）。

语法

CODEDBY(item) 

示例

SELECT CODEDBY(ae_term.ae) FROM adverse_event 

LASTCODED

返回条目的上次编码日期（编码请求上次处于已编码或已自动编码状态的日期）。

语法

LASTCODED(item) 

示例

SELECT LASTCODED(ae_term.ae) FROM adverse_event 

参考对象

以下参考对象函数可用：

KEYMATCH

如果 related_item 和 related_key 之间相匹配，则返回 true。

KEYMATCH 利用清单文件中的 related_item 和 related_key 对条目进行匹配。如果表单中的 related_item 与参考数据中的 related_key 相匹配，则 keymatch 列的值为 true。否则，值为 false。

在 KEYMATCH 函数的第一个参数中指定表单的源时（SourceName.FormName），第一个参数中指定的 SourceName.FormName 必须完全匹配 FROM 子句中的 SourceName.FormName。

语法

KEYMATCH(form_name, reference_object_name) 

控制流

以下控制流函数可用：

IF

评估逻辑表达式，并在为真或为假时返回一个值。此函数在比较两个值时非常有用。

语法

IF(<Expression>, <Return_Value_True>,<Return_Value_False>) 

示例

SELECT IF(Item1=Item2,1,0) as comp FROM Form1 

如果条目 1 等于条目 2，则此查询返回“1”。如果条目 1 不等于条目 2，则此查询返回“0”。

CASE

评估逻辑表达式，并在为真或为假时返回一个值。此函数在比较多个值时非常有用。

语法

CASE WHEN(<Expression>) THEN '<Return_Value>' ELSE '<Return_Value>' END 

示例

SELECT CASE WHEN(Item1=Item2) THEN 'a' WHEN Item1 > Item2 THEN 'b' ELSE 'c' END FROM Form1 

当条目 1 等于条目 2 时，此查询返回“a”，当条目 1 大于条目 2 时，返回“b”，如果以上条件都不成立，则返回“c”。

IFNULL

评估 DATAITEM，并在 DATAITEM 为 NULL 时返回一个值。

语法

IFNULL(DATAITEM,<Return_Value>)` 

示例

SELECT IF(IFNULL(Item1,0)=Item2,1,0) as comp FROM Form1 

如果条目 1 与条目 2 相等，或者条目 1 为 NULL 且条目2 等于 0，则此查询返回”1“。否则，此查询返回”0“。

字符串

以下字符串函数可用：

CONCAT

返回连接参数后得到的字符串。

语法

CONCAT(DATAITEM, 'string') 

示例

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

此查询返回受试者 ID、下划线和姓名首字母条目值的拼接字符串，例如 101-1001_CRJ。

CONCAT_WS

返回带有分隔符的参数

语法

CONCAT_WS ( separator, DATAITEM1, DATAITEM2) 

示例

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

GROUP_CONCAT

从组中返回非 NULL 值的拼接字符串。

语法

GROUP_CONCAT ( DATAITEM) 

示例

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

返回第一个非 NULL 参数

语法

COALESCE (DATAITEM1, DATAITEM2, ...)

示例

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

LENGTH

返回参数传递的值或 DATAITEM 的长度。

语法

LENGTH(DATAITEM) 

示例

SELECT LENGTH(Item1) FROM Form1 

此查询返回表单 1 中条目 1 值的长度。

TRIM

删除 DATAITEM 的前导和尾部空格。

语法

TRIM(DATAITEM) 

示例

SELECT TRIM(Baseline_X-ray.Interpretation) FROM Baseline_Exam 

此查询返回基线检查表单中解释条目的值，删除了所有前导和尾部空格。

LTRIM

删除 DATAITEM 的前导空格。

语法

LTRIM(DATAITEM) 

示例

SELECT LTRIM(Baseline_X-ray.Interpretation) FROM Baseline_Exam 

此查询返回基线检查表单中解释条目的值，删除了所有前导空格。

RTRIM

删除 DATAITEM 的尾部空格。

语法

RTRIM(DATAITEM) 

示例

SELECT RTRIM(Baseline_X-ray.Interpretation) FROM Baseline_Exam 

此查询返回基线检查表单中解释条目的值，删除了所有尾部空格。

UPPER

将 DATAITEM 转换为大写。

语法

UPPER(DATAITEM) 

示例

SELECT UPPER(Item1) FROM Form1 

此查询以大写形式返回姓名首字母条目的值，例如，从“cfa”转换为“CFA”。

LOWER

将 DATAITEM 转换为小写。

语法

LOWER(DATAITEM) 

示例

SELECT LOWER(Item1) FROM Form1 

此查询以小写形式返回条目 1 的值。

LEFT

返回最左边 len（数量）个字符。

语法

LEFT(DATAITEM,len) 

示例

SELECT LEFT(Creation_Criteria.Initials,1) FROM Demographics 

此查询返回姓名首字母条目中最右边的一个字符（受试者姓名的最后一个首字母）。

RIGHT

返回最右边 len（数量）个字符。

语法

RIGHT(DATAITEM,len) 

示例

SELECT RIGHT(@HDR.Subject.ID,4) FROM Demographics 

此查询返回受试者 ID 中最右边的 4 个字符。在 Cholecap2019_Phase1 研究中，这是受试者的四位数标识符，例如 1001，附加到研究中心编号构成了受试者 ID（例如 104-1001）。

MID

返回从指定位置开始的子字符串

语法

MID ( DATAITEM, start index, length ) 

示例

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

REGEXP

判断字符串是否与正则表达式匹配

语法

REGEXP 'regular expression pattern' 

示例

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

REPLACE

替换指定字符串

语法

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

示例

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

SUBSTR

返回指定的子字符串

语法

SUBSTR (DATAITEM, start index, length) 

示例

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

日期和时间&

CDB 支持三种不同的方式来查看通过 EDC 捕获的日期和日期时间：推断、原始和研究中心标准化。推断日期是指应用逻辑来推断日期或日期时间中任何缺失部分（未知）的日期。在 EDC 中，研究可以收集日期和日期时间中未知的日、月或小时部分。在 CDB 中，如果日期包含未知部分，CDB 首先会对这些未知部分应用推断逻辑。如果月份未知，CDB 会将其推断为该年的第一个月。如果日期未知，CDB 会将其推断为当天的第一个小时。所有日期和日期时间默认以完整日期和日期时间形式返回，其中未知部分通过推断估计。这种处理方式便于查看和清理日期和日期时间的比较。

CDB 还可以返回使用 RawDate() 函数输入的日期和日期时间的确切表示。该函数始终以字符串形式返回输入的确切值，它不会替换任何未知部分。此外，CDB 还可以使用 SiteNormalizedDate() 函数将日期或日期时间作为研究中心标准化值返回。此函数返回经过调整的日期和日期时间，以匹配捕获这些数据的研究中心的时区。

以下函数可用于日期和时间：

ADDDATE

从给定日期或日期类型的 DATAITEM 中添加一个日期/时间间隔（得到一个更晚的日期）并返回该日期/时间。

可以提供一个天数，或者使用 INTERVAL 指定一个数值和单位。

语法

ADDDATE ('date', days) 

或

ADDDATE('date', INTERVAL value unit) 

CQL 支持以下间隔单位：

• 微秒（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）

示例

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

此查询返回随机化日期值后 5 天的日期。

SUBDATE

从给定日期或日期类型的 DATAITEM 中减去一个日期/时间间隔（得到一个更早的日期）并返回该日期/时间。

可以提供一个天数，或者使用 INTERVAL 指定一个数值和单位。

语法

SUBDATE ('date', days) 

或

SUBDATE('date', INTERVAL value unit) 

CQL 支持以下间隔单位：

• 微秒（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）

示例

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

此查询返回随机化日期值前 5 天的日期。

TIMESTAMPDIFF

从日期时间表达式中减去一个时间间隔。

语法

TIMESTAMPDIFF(month, date, date) 

示例

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

DATEDIFF

返回两个日期之间的天数差。

语法

DATEDIFF('date', 'date') 

示例

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

此查询返回随机化日期条目的日期值与 2019 年 12 月 12 日之间的差异（以天为单位）。

DATE_FORMAT

以指定格式返回给定日期。请参阅以下日期说明符列表。

语法

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

示例

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

此查询以 %Y-%m-%d 格式返回事件日期（例如 2019-12）

SITENORMALIZEDDATE

CDB 默认返回在 EDC 中输入的日期时间值。SiteNormalizedDate 函数返回标准化为 UTC 时间的日期时间。

语法

SiteNormalizedDate(date) 

示例

select @HDR.Site.Name, @HDR.Site.Number, @HDR.Site.Timezone, @HDR.Site.Country, @HDR.Site.CountryName, @HDR.Subject.Name, AESTDT, SiteNormalizedDate(AESTDT) as 'Site Normalized Start Date' from `EDC`.`AE` where @Form.Status = 'submitted__v'or @HDR.Event.Status in ('did_not_occur__v') 

在此示例中，受试者在 3 个时区之间的 AE 开始日期相同。AESTDAT 将返回在 EDC 中输入的日期时间值，而 SiteNormalizedDate 函数将返回标准化为 UTC 时间的日期时间值。

RAWDATE

以原始格式返回给定的日期或日期时间。在原始格式中，会不经任何修改显示部分日期和时间。

如果在研究中心的本地时区中输入了日期，则 RawDate 将返回研究中心的本地时区。

请参阅以下原始格式的部分日期列表。

语法

RawDate(<date>) 

示例

select @HDR.Site.Name, @HDR.Site.Number, @HDR.Site.Timezone, @HDR.Site.Country, @HDR.Site.CountryName, @HDR.Subject.Name, AESTDT, SiteNormalizedDate(AESTDT) as 'Site Normalized Start Date', RawDate(AESTDT) as 'Raw Start Date' from `EDC`.`AE` where @Form.Status = 'submitted__v'or @HDR.Event.Status in ('did_not_occur__v') 

在此示例中，CQL 以原始格式返回 AESTDT 作为“原始开始日期”。

此查询以原始格式返回受试者 DOB 条目的日期值。

SDTMDATEFORMAT

以 SDTM（ISO 8061）格式返回给定日期或日期时间，并自动处理日期的未知部分（如果存在）。该值以字符串形式返回。请参阅以下 SDTM 格式的部分日期列表。

语法

SDTMDateFormat(<date>) 

示例

SELECT SDTMDateFormat(mh.condition_start_date) from EDC.medical_history 

此查询以 SDTM 格式返回条件开始日期条目的日期值，并处理任何未知部分。

STR_TO_DATE

给定一个字符串及其日期格式，返回一个日期。请参阅以下日期说明符列表。

语法

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

示例

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

此查询返回 2021 年 1 月 1 日作为日期。

CURDATE

以 YYYY-MM-DD 格式返回当前日期

示例

SELECT CURDATE() FROM Demographics 

此查询返回当前日期。

NOW

以“YYYY-MM-DD hh:mm:ss”格式返回当前日期和时间。

示例

SELECT NOW() FROM Demographics 

此查询返回当前日期和时间。

YEAR

返回给定日期的年份。

语法

YEAR('date') 

示例

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

此函数返回“2019”，即 2019 年 12 月 12 日的年份。

MONTH

返回给定日期的月份。

语法

MONTH('date') 

示例

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

此函数返回“12”，即 2019 年 12 月 12 日的月份。

WEEK

返回给定日期在一年中的周数。

语法

WEEK('date') 

示例

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

此函数返回“50”，即 2019 年 12 月 12 日在一年中的周数。

DAY

返回给定日期的日期。

语法

DAY('date') 

示例

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

此函数返回“12”，即 2019 年 12 月 12 日的日期。

HOUR

返回给定时间的小时。

语法

HOUR('time') 

示例

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

此函数返回“10”，即 10:05:03 的小时。

MINUTE

返回给定时间的分钟。

语法

MINUTE('time') 

示例

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

此函数返回“05”，即 10:05:03 的分钟。

SECOND

返回给定时间的秒数。

语法

SECOND('time') 

示例

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

此函数返回“03”，即 10:05:03 的秒数。

LAST_DAY

返回给定日期所在月份的最后一天。

语法

LAST_DAY('date'), LAST_DAY(DATAITEM) 

示例

SELECT LAST_DAY(@HDR.Event.Date) 

此查询返回事件日期所在月份的最后一天。例如，如果事件日期为 2019 年 11 月 15 日，则此查询返回“30”，即 11 月的最后一天。

未知日期

Workbench 支持在 CQL 查询中引用来自 Vault EDC 或第三方数据的未知日期和日期时间。Workbench 允许以下未知日期和时间选项：

• 月、日和时间
• 日和时间
• 时间

默认情况下，Workbench 将未知日期部分转换为该月的第一天或该年的第一月，将未知时间转换为 00:00:00。这可以普遍处理日期，而无需担心未知日期和时间。

Unknown

如果日期包含未知部分（M、D 和 T），则返回未知的日期部分，否则返回字符串 COMPLETE。

语法

Unknown(Date) 

示例

Unknown(edc.mh_condition_start_date) 

在此示例中，病史表单中的条件开始日期条目允许使用未知的月份、日期和时间。如果 EDC 用户输入了“07-17-2021 5:15”，该函数将返回 COMPLETE，因为输入的日期没有未知部分。如果 EDC 用户输入了“06-?-2005 ?”，该函数将返回 D,T，因为该日期的日期和时间未知。

UnknownImpute

此函数允许用户指定表示未知部分的方式。给定一个条目，该函数使用原始日期值确定日期是否包含未知部分，然后应用提供的逻辑来处理未知部分。结果是日期数据类型的值。如果值不包含任何未知部分，则该函数将返回默认日期。

语法

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

推断类型：

必须输入每种推断类型，即使日期或日期时间不允许包含这些未知部分。请注意，输入类型必须用单引号（‘）括起来。

• 天：
  • FIRST DAY：月份的第一天。
  • LAST DAY：月份的最后一天。
  • MID DAY：月份的第 15 天。
• 月：
  • FIRST MONTH：一月（1）
  • LAST MONTH：十二月（12）
    • MID MONTH：六月（6）
• 时间：
  • FIRST HOUR：00:00（24 小时制）
  • LAST HOUR：23:59（24 小时制）
  • MID HOUR：12:00（24 小时制）

示例

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

在此示例中，AE 开始日期时间条目允许时间包含未知部分。此查询返回未知时间设置为 12:00 AM 的 AE 开始日期时间。例如，如果在 EDC 中输入的日期时间是“Jul 22, 2021 ?”，则 CQL 将返回“Jul 22, 2021 12:00 AM”。

日期和时间说明符&

以下是在 CQL 中可用的日期和时间说明符：

原始格式日期

SDTM 格式日期

其他

以下函数也可用于 CQL 查询：

CAST

Cast 函数采用数据条目或表达式参数，并以指定的数据类型返回结果。

语法

CAST(item AS data_type) CAST (expression AS data_type 

以下数据类型可在 CAST 函数中使用：

• 二进制（Binary）
• 字符（CHAR）
• 日期（DATE）
• 日期时间（DATETIME）
• 时间（TIME）
• 十进制（DECIMAL）
• SIGNED
• 未签名（UNSIGNED）

示例

SELECT CAST(Exclusion_Criteria.Hypersensitivity_Constituents as CHAR) FROM Demographics 

此查询以文本（字符）形式返回超敏反应成分代码列表类型条目中的代码列表条目值（Y 或 N）。

FIELD

返回字符串列表中条目值的索引或位置。这对于自定义结果集中可能值列表的顺序非常有用。

语法

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

示例

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

此查询返回受试者级标题信息以及途径和剂量条目值，按口服、鼻用、外用的途径排序。

UNION

Union 将多个 SELECT 语句的结果合并为一个结果集。CQL 使用第一个 SELECT 语句中列出的列名作为列名。针对第一个 SELECT 语句中的列，后续 SELECT 语句中的相应列必须为相同的数据类型。例如，如果第一个语句的第二列是整数，则第二个 SELECT 语句的第二列也必须是整数。

UNION 返回集合中查询的所有可能结果行，UNION ALL 也是如此。可以使用 UNION DISTINCT 仅检索集合中查询的唯一结果行。UNION DISTINCT 仅删除 100% 匹配的重复行。

语法

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

示例

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 是一个关系运算符，可用于将表的列转换为列值。UnPivot 中不能包含任何 @HDR 值，也不能在 WHERE、GROUP BY 或 ORDER BY 子句中引用任何列转行数据条目。

语法

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 

示例：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 

示例：多个 Unpivot 部分

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：

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 

不支持的使用情况：

当前版本不支持以下使用情况：

• 子查询中的 UnPivot
• Union 中的 UnPivot
• 在 UnPivot 中引用 @Form 或 @ItemGroup 属性

子查询中的 UnPivot：

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 

Union 中的 UnPivot：

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; 

其他可用函数

以下 SQL 函数也可在 CQL 中使用：

CQL 错误

如果收到通用的 CQL 运行时错误（错误代码 0001 到 0010），请联系 Veeva 支持部门。