臨床クエリ言語 (CQL) リファレンス

Veeva 臨床データベース (CDB) の臨床データへのアクセス、検索、対話に、臨床クエリ言語 (CQL) を使用することができます。このガイドでは、CQL の活用方法について、その構文、構造、句、演算子の概要について説明しています。CQL クエリは多くの構文要素において構造化照会言語 (SQL) と共通ですが、CQL ステートメントでは合理化され、臨床に焦点を当てた構文を使用して、EDC データ専用のクエリを実行することができます。

CDB ワークベンチでデータを照会すると、CQL ステートメントが渡されます。CQL ステートメントでは FROM 句で照会するオブジェクトが指定され、SELECT 句で取得するフィールドが指定され、WHERE 句で適用する任意のフィルタが指定されます。

次のクエリの例は、スタディ「Cholecap 2019 フェーズ 1」のフォーム Demographics および Inclusion Criteria からすべてのデータ項目を返すものです。

SELECT *
FROM F.Demographics, F.Inclusion_Criteria

CQL の臨床コンテンツ

CQL は、一般的な臨床データのパターンと関係性を認識します。この「コンテキスト」は、SQL などの汎用クエリ言語よりもシンプルな言語構文を提供します。CQL は、次の定義済みオブジェクトと関係性を認識します:

照会可能なデータ

CQL でクエリを作成するには、照会可能なデータとそのデータの構造を理解することが重要です。DESCRIBE キーワードと SHOW キーワードを使用すると、CQL で照会可能なデータのリストを返すことができます。

Describe

DESCRIBE キーワードを使用してスキーマに関する詳細を返すクエリを作成することができます。

CQL は、次のデータ要素のDESCRIBE をサポートしています:

• ヘッダー
• フォーム
• 項目グループ
• 項目
• REFERENCE_OBJECT

DESCRIBE を呼び出すと、CQL はそのテーブルまたはオブジェクト内のすべてのフィールドを返します。

DESCRIBE フォーム

フォームに対して DESCRIBE を呼び出すと、フォームに含まれるすべての項目 (名前とデータ型を含む) のリストが返されます。

DESCRIBE Form VITALS

このクエリは、スタディ「Cholecap 2019-フェーズ 1」の Vitals フォームにあるすべての項目のリストを返します。

表示

また、SHOW キーワードを使用してクエリを作成すると、可能な値のリストを返すこともできます。イベントグループ、イベント、フォーム、コードリストの各項目に SHOW を適用することができます。SHOW を使用して、現在の Vault 内のスタディのリストを返すこともできます。

SHOW Studies

SHOW を使用して、現在の Vault 内のスタディのリストを返すことができます:

SHOW STUDIES

SHOW EVENTGROUPS

SHOW を使用して、現在のスタディ内のイベントグループのリストを返すことができます:

SHOW EVENTGROUPS

SHOW EVENTS

SHOW を使用して、現在のスタディ内のイベントのリストを返すことができます:

SHOW EVENTS

SHOW FORMS

SHOW を使用して、現在のスタディ 内のフォームのリストを返すことができます:

SHOW FORMS

SHOW CODELIST ITEMS

SHOW を使用して、与えられたコードリスト内のコードリストのアイテムのリストを返すことができます。コードリストタイプのデータアイテムの名前を指定する必要があることに注意してください。

SHOW CODELIST <DATAITEM>

SHOW VIEWS

SHOW を使用して、スタディの内のビューのリストを返すことができます。

SHOW Views

SHOW Reference

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 の制限事項

カスタムリストでビューを参照する際、以下の制限事項があります:

• ビューで別のビューを参照することはできません。
• ビューの @Form および @ItemGroup 属性を射影またはフィルタ (WHERE 句) に含めることはできません。
• On Subject ALIGN を使用する場合、ビューに UNALIGN を使用することはできません。
• On Subject を使用しているビューと、他のフォームまたはビューを結合する場合、ビューには On Subject を使用する必要があります。

ビュー内の被験者の結合

ビューに他のフォームまたはビューを結合する場合、ビューには On Subject ALIGN および On Subject UNALIGN を使用することができます。

ビューとフォーム間で症例を使用した結合を実行する場合、ビューの ビュー定義 に 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

現在、このビューを VitalSigns という別のフォームと結合して、被験者の身長、体重、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、related_key プロパティのキーと値がペアになったテーブルを取得します。

ReferenceRelations <Reference_Object_Name>

品目がテキスト型参照オブジェクトの場合、CQL で以下の関数を使用することができます:

• CONCAT
• 長さ
• TRIM
• LTRIM
• RTRIM
• UPPER
• LOWER
• 左
• 右

品目が日付型参照オブジェクトの場合、CQL で以下の関数を使用することができます:

• ADDDATE
• SUBDATE
• DATEDIFF
• DATE_FORMAT
• SITENORMALIZEDDATE
• RAWDATE
• SDTMDATEFORMAT
• STR_TO_DATE
• CURDATE
• 今すぐ
• 年
• 月
• 週
• 日
• 時間
• 分
• 第2週
• LAST_DAY

参照オブジェクトへの CQL の制限事項

参照オブジェクトを使用する場合、CQL の制限事項が適用されます。

次の関数は、参照データでは使用できません:

• 機能停止
• ILB
• ラベル
• LASTCODED
• LastModifiedDate
• ロック済み
• 署名済み
• ステータス

クエリの構文と構造

CQL クエリはタプル (列の順序が定義されたデータのテーブル) を返します。タプルを返すには、CQL クエリで以下を指定する必要があります:

• PROJECTION: 取得したい特定のデータ要素。
• SELECTION: 射影のデータ要素が存在する特定のデータテーブル。
• FILTER (任意): 返されるデータ要素の照合基準 (これらはタプルに含まれる必要はなく、スタディ内の暗黙的な関係を通じて参照可能です)。
• ORDER (任意): タプル内で行が返される順序。

射影

CQL クエリの PROJECTION は、取得するデータ要素と、結果セット内の要素の順序を指定します。フォールドのリストは DATAITEM 名を使用して明示的に指定したり、ワイルドカードショートカットを使用することもできます。

射影の記述には、SELECT 文を使用します。

ワイルドカード

SELECT 文でワイルドカードのアスタリスクを使用すると、FROM 句で指定したフォームコンテキスト内の関連する DATAITEMS のすべての列をレイアウト順に返すことができます。

SELECT *
FROM Demographics

コンテキスト修飾アスタリスク

ワイルドカードのアスタリスクの代わりに、コンテキスト修飾アスタリスクを使用して、修飾子のコンテキスト内のすべての DATAITEMS を返すことができます。

現在のリリースでは、FORM と ITEMGROUP の修飾子のみがサポートされています。

SELECT <Qualifier>.*
FROM <Form_Name>

SELECT Creation_Criteria.*
FROM Demographics

このクエリの例では、スタディ「Cholecap 2019 フェーズ 1」の Demographics フォームの Creation Criteria 項目グループにあるすべての DATAITEMS が返されます。

データ項目

または、取得する特定の DATAITEMS を指定することもできます。射影で指定したフォーム上に存在する任意の DATAITEM を選択することができます。フォームを 1 つだけ指定した場合、これらの列を直接参照することができます。フォームを複数指定した場合、各 DATAITEM をフォーム名またはエイリアスで修飾する必要があります。

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

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

このクエリの例では、Demographics フォームの生年月日項目と Enrollment フォームのランダム化した数字項目のすべての値が返されます。

データ関数

また、結果セットの特定の列として返すことができる関数を指定することもできます。関数では DATAITEMS を引数に使用することができ、また、上記の多くのコンテキストを使用することができます。

関数を使用して、名前と ID を除く DATAITEM のすべてのプロパティにアクセスすることができます。

関数、使用できる引数、定義の完全なリストについては、以下を参照してください。

SELECT STATUS(Demographics.Subject_Date_of_Birth)
FROM Demographics

このクエリの例では、スタディ「Cholecap 2019 フェーズ 1」の Demographics フォームの 生年月日項目のステータスが が返されます。

Distinct

DISTINCT を使用すると、特定の列または列の集合全体の値の一意のインスタンスのみを返すことができます。

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

SELECT DISTINCT Dosing.Dosage_Amount
FROM Dosing

このクエリでは、スタディ「Cholecap 2019 Phase 1」の 投薬 フォームの投薬量項目からすべての一意の値が返されます。

エイリアス (AS)

AS を使用して、クエリ内のフォームやデータ項目のエイリアスを定義することができます。エイリアスは、名前の長いフォームまたはデータ項目を繰り返し参照する場合に便利です。

射影した特定の列にエイリアスを指定することができます。データ項目に別名を指定した場合、ワークベンチによってリストの別名で項目の列の名前が変更されます。データ項目のエイリアスは、次の例のような長いデータ項目名に対して特に有効です。データ項目に短いエイリアスを使用することで、コアリストで結果を表示際の水平スクロールの必要性を減らすことができます。

エイリアスは、フォームに指定することもできます。これは、1 つのクエリでフォームを複数回参照する場合に有効です。エイリアスを設定した後は、フォーム名ではなくエイリアスでフォームの参照が可能になります。

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

このクエリの例では、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 表記を使用するには、リンク元フォーム上の FormLink 項目の名前と、リンク先フォーム上で対象とする項目を指定する必要があります。

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

リンク先の項目が複数の項目グループにまたがって使用されている場合、項目グループの名前も指定する必要があります。

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

リンク元のフォームが複数の項目グループにまたがって同じ FormLink 項目を使用する場合、項目グループのフォームリンクの名前を指定する必要があります。

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

次の例では、AE フォームに 2 つの FormLink (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 フォームの間にあります。リンク先フォーム CM の CMONGO 項目には、@FormLink.ae_to_cm.CMONGO を使用してアクセスします。

FormLink ae_to_mh は、ae_to_mh 項目と MH フォームの間にあります。リンク先フォーム MH の MHSTDAT 項目には、@FormLink.ae_to_mh.MHSTDAT を使用してアクセスします。

WHERE 句、Order By 句、Group By 句には、@FormLink 表記で示される項目を使用することができます。

FormLink 項目では、次の CQL 機能がサポートされています:

• UNION および UNION ALL
• On Subject ALIGN および UNALIGN
• サブクエリ
• UNPIVOT

クエリのプロパティ

選択またはフィルタ句に、クエリのプロパティを対象にすることができます。@QRY または @QRYMSG にドット表記でプロパティ名を追加してそのプロパティを取得します。例えば、クエリの ID を取得するには、@QRY.ID を使用します。

@QRY

@QRY を使用してクエリオブジェクトにクエリが可能なプロパティを以下に紹介します:

@QRYMSG

@QRYMSG を使用してクエリメッセージオブジェクトにクエリが可能なプロパティを以下に紹介します:

選択

選択 (FROM) は、射影が動作するフォームの名前を指定するためのメカニズムです。ワイルドカードのアスタリスクでは、選択によって射影したフォームの順序も指定されます。

CQLでは、FROM は常にデータの選択元のフォームコンテキストを指します。FROM 句で他のコンテキストを使うことはできません。

ヘッダーは @HDR 変数からアクセスできるため、FORM と DATAITEMS が不要な場合、ヘッダーの任意の要素を単独で選択することもできます。これらのタイプの選択を使用して、症例ごとにオペレーショナルデータのみをリストした結果を作成することができます。射影に @HDR のみが含まれる場合、FROM を指定せずにこの選択をフィルタすることができます。ただし、エイリアスとサブクエリを使用するには、FROM 句を含める必要があります。

Join on Subject

デフォルトでは、CQL は被験者とイベントの両方を結合します。このため、異なるイベントにある 2 つのフォームから選択した場合、CQL は被験者ごとに 1 行を返すのではなく、フォームごとに 1 行返します。この場合、被験者のみで結合することで、両方のフォームからのデータを含む被験者ごとに 1 行を返すことができます。

被験者で結合するには、選択のフォーム ID のリストに on subject を追加します。使用できる ID の数に制限はありませんが、on subject は指定されたすべてのフォーマットに適用されます。

on subject は複数のイベントグループまたはイベントからデータを返すことができるため、on subject と一緒に使用すると CQL は @HDR.EventGroup または @HDR.Event プロパティに対して null を返します。これらのプロパティを取得する必要がある場合、各フォームのフォームエイリアスで選択します。

次のクエリの例は、被験者 ごとに @HDR.Subject プロパティ、被験者のイニシャル、被験者の DOB、身長、体重、脈拍数、最高血圧、最低血圧、体温を持つ 1 行を返します。クエリの例で被験者による結合をしない場合、CQL は Demographics フォームの項目の行と Vitals フォームの項目の 2 行目を返します。

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 修飾子

繰り返しのフォームまたは項目グループで on subject を使用する場合、結合されたシーケンスのデータ (aligned) を返すか、シーケンスが位置揃えされていない (unaligned) 場合でもデータを返すかを選択できます。例えば、統計情報フォームと繰り返しの有害事象フォームからの情報を表示する場合、CQL は 統計情報フォームからのデータを被験者のすべての有害事象フォームインスタンスに対して返される行の返します。この動作は、シーケンスが位置揃えされていない状態でもデータを返しているため、「unaligned」となります。UNALIGN はデフォルトの動作であるため、CQL ステートメントにこの修飾子を明示的に含めるかどうかは任意となります。この例では、「aligned」の動作は、被験者のすべての有害事象フォームインスタンスに対して返される最初の行についてのみ、統計情報フォームからデータを返します。位置揃えする場合、ALIGN 修飾子を含めます。

高度な結合

CQL は高度な (方向性のある) 結合にも対応しています。高度な結合を使用すると、最初に @HDR に結合することなくフォームを結合できます。

LEFT JOIN: 1 番目 (左) の表の全レコード、および 2 番目 (右) の表の一致するデータ RIGHT JOIN: 2 番目 (右) の表のすべてのレコード、および 1 番目 (左) の一致するデータ。 INNER JOIN: 一致が見つかった両方の表のレコードのみ

現在のリリースでは、CQL は外部結合をサポートしていません。

詳細結合を使用して参照データを他のデータに結合することはできません。

例: フォーム間の Left Join

以下の CQL 文では、フォーム定義属性 @Form.Subject に基づき、AE フォームと CM フォームの間で LEFT JOIN が存在します。Subject.Name はフォーム定義の一部であり、@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'

結合条件は @Form 属性 (@Form.Subject.Name) に基づいており、プロジェクションには @HDR 属性が含まれていないため、@HDR に結合しなくても正常に機能します。

例: フォームおよび @HDR 間の Left Join

以下の CQL クエリでは、プロジェクションに @HDR 属性があるため、@HDR との結合が必須です。以下のクエリは、Event.ID に基づく @HDR と AE の LEFT JOIN です - この LEFT JOIN の出力は、Event.ID と Subject.ID に基づいて CM と再度 LEFT JOIN されます。

以下の 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 を返します。

サブクエリの結合

また、一般的な JOIN 構文ではなく、定義した 2 つの SELECT 文をサブクエリを使用して結合することも可能です。結合はコンマ (,) を使用して表現し、2番目のサブクエリは1番目に左外部結合されます。サブセレクトにエイリアスを付けることもできますが、必須ではありません。これらの結合には、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 ステートメントにコメントを含めることもできます。コメントを含めるには、ダッシュを 2 つとスペースを 1 つ (--) 入力し、その後にコメントを入力します。

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 プロジェクトに少なくとも 1 つの集計関数が必要です。

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

結果のグループ化

CQL では、射影に少なくとも1つの集計関数が含まれていれば GROUP BY 構文を使用することができます。

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

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

結果の構造

CQLの結果は、射影で指定した要素に基づいて特定のシーケンスで返されます。CQL では、射影に含めるものをすばやく指定するための 3 つの方法 (ヘッダー、ワイルドカード、修飾ワイルドカード) を使用することができます。クエリの結果で返される追加の列を指定することができます。

また、射影にどの DATAITEMS を含めるかによって、実行データの並び順も決まります。

フォームおよびデータ項目の結果

SELECT @HDR 表記と同様に、残りの FORM および DATAITEMS は SELECT * または SELECT ItemGroup.* を使用して返すことができます。結果セットには既定の列の順序があるため、これらの表記を使用する場合このセクションの行は、(射影に含まれている場合) ヘッダーに適用された並び順に従って、特定の並び順で返されます。

クエリでワイルドカード表記を使用しない場合、射影内で返されるデータの列を指定する必要があります。結果の並び順は、射影に含めた要素に基づいて、以下に示すソートオプションの階層と一致します。

注: ソート階層に含まれるオブジェクトが存在する場合、その特定の列が指定されていない場合もソート階層のルールは適用されます。

SELECT * のデフォルトの列順序

列のレイアウトは、以下に示すフォーム属性から始まり、フォームのレイアウト順序に従います。

• フォーム名
• フォームの SeqNum (繰り返しフォームの場合のみ)
• 項目グループ
• SeqNbr (繰り返し項目グループの場合のみ)
• 項目 1
• 項目 2
• ItemN

SELECT ItemGroup.* のデフォルトの列順序

列のレイアウトは、以下に示すフォームおよび項目グループ属性から始まり、項目グループのレイアウト順序に従います。

• フォーム名
• フォームの SeqNum (繰り返しフォームの場合のみ)
• 項目グループ
• SeqNbr (繰り返し項目グループの場合のみ)
• 項目 1
• 項目 2
• ItemN

列のタイトル

CQL は、特定のパターンを使用して各カラムにタイトルを付けます。

• @HDR 列: Object.Property (例: @HDR.Site.Nameの「Site.Name」)
• データ項目: 項目定義の名前
• エイリアス: エイリアス名が付けられたデータの場合、CQL はそのエイリアス名を列のタイトルとして使用します。

フォームおよび項目グループは繰り返し可能であるため、Form.Name、ItemGroup.SeqNbr のように、ヘッダー結果と同じ Object.Property の表記に従います。

ワイド & コンパクトな結果

CQL は常に結果セットの先頭に @Form および @ItemGroup の情報を付加したデータ項目を返します。これがフォームヘッダーです。この情報から、特定のデータ項目がどのフォームと項目グループから収集されたものであるかを判断することができます。フォームヘッダー以外にも、データ項目のワイド形式またはコンパクト形式の調整が可能です。デフォルトでは、CQL はワイド形式で結果を返します。

ワイド形式では、(クエリのコンテキストで) 収集されたすべてのフォームまたは項目グループに対して、すべてのデータ項目が独自の列で返されます。

コンパクト形式では、データ項目がどの形式または項目グループで収集されたかにかかわらず、データ項目の結果が 1 つの列で表示されます。これは、項目の値を比較する場合に有用です。

結果をコンパクト形式で返すには、SELECT 文で COMPACT を指定します。

SELECT COMPACT @HDR, Creation_Criteria.*
FROM Demographics

このクエリは、統計情報フォームの作成基準項目グループの @HDR 要約と DATAITEMS をコンパクト形式で返します。

例: 複数フォームによる項目の再利用

この例では、スタディは統計情報とインフォームドコンセントという 2 つのフォームを持ち、双方とも生年月日項目を使用しています。

WIDE

COMPACT

例: 繰り返しフォーム

この例では、スタディに繰り返し有害事象フォームがあります。

WIDE

COMPACT

例: 繰り返し項目グループ

この例では、スタディに身体部位別検査の繰り返し項目グループを持つ診察フォームがあります。

WIDE

COMPACT

ログフォームイベント

EDC 内の一部のイベントには、イベント日が関連付けられていません。これらのイベントは「ログ」イベントで、これらのイベント内のFORMSは「ログフォーム」です。ログフォームの一般的な例は、有害事象および併用薬フォームなどです。これらのログフォーム内の情報は、スケジュールされたイベントとは別に収集されます。

ログフォームが射影に含まれている場合、CQL はこれらのフォームをログイベントの下にグループ化します。ログイベントは、常に結果セット内の他のすべてのイベントグループの後に表示されます。

関数

CQL では、特定の DATAITEM または FORM に関する追加情報を提供するさまざまな関数を使用することができます。これらの関数は、CQL の射影、フィルタ、Order By 句に含めることができます。CQL では、臨床データのクエリに特化した関数のセットも使用することができます。

• 臨床関数
• コーディング関数
• フロー制御関数
• 文字列関数
• 日時関数
• その他の関数

試験

次の臨床関数を使用できます:

説明

指定されたデータ項目の項目定義に記載されている説明を返します。

構文

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

機能停止

与えられたデータ項目が現在固定されているか否かによって、true または false を返します。親 (項目グループとフォーム) から継承されたものである場合、この値は 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 が意図的に空白のままとしてマークされているかどうかに基づいて true または false を返します。親フォームから継承されたものである場合、この値は 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 で意図的に空白のままとマークされていない投薬量項目の行を返さないようにします。

ラベル

指定された DATAITEM の項目定義に記載されているラベルを返します。代替ロケールを指定してローカライズされたラベルを返すこともできます。デフォルトでは、LABEL() は現在のユーザのロケールのラベルを返します。Vault CMDS のロケール値の一覧についてはこちらを参照してください。

構文

LABEL(DATAITEM,['locale'])

例

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

このクエリは、登録フォームのランダム化番号項目のローカライズ (es_PRの場合、またはプエルトリコ) された項目用のラベルを返します。

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

登録フォームのランダム化番号項目の最終更新日を返します。

ロック済み

与えられた DATAITEM が現在ロックされているか否かによって、true または false を返します。親 (項目グループとフォーム) から継承されたものである場合、この値は NULL になります。

構文

LOCKED(DATAITEM)

例

SELECT LOCKED(Creation_Criteria.Initials)
FROM Demographics

このクエリは、統計情報フォームのイニシャル項目がロックされている場合に true を返します。イニシャル 項目がロック解除されている場合、このクエリは false を返します。

SDV

DATAITEM がソースデータ検証 (SDV) されている場合は true を返し、そうでない場合は false を返します。

構文

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

署名済み

与えられた DATAITEM が署名されているか否かによって、true または false を返します。親 (項目グループとフォーム) から継承されたものである場合、この値は NULL になります。

構文

SIGNED(DATAITEM)

例

SELECT SIGNED(Creation_Criteria.Initials)
FROM Demographics

このクエリは、統計情報フォームのイニシャル項目が署名されている場合に true を返します。イニシャル項目が署名されていない場合、このクエリは false を返します。

ステータス

DATAITEM のステータスを返します。この関数を使用して、STATUS() の代わりにドット表記と @ Form 変数を使用してフォームのステータスを返すこともできます。2 つ目の例をご覧ください。

構文

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(DATAITEM)

例

SELECT TRANSLATED(Vitals.Temperature)
FROM Vitals

このクエリは、施設ユーザが摂氏で体温を入力した場合でも、バイタルフォームの体温項目から華氏 (デフォルトの単位) で値を返します。

FILL

CQL NULL を返すセルに対して、非繰り返し項目グループの項目から指定された　FILL 値を返します。この機能はプロジェクションでのみ使用可能です。

構文

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 の Labs モジュールのデータを使用することで以下の関数を使用することができます。

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

ラボの場所の LAB タイトルを返します。LBLOC は LAB ID のみを返します。LBLOC 項目はこの関数で使用します。

構文

LabLocTitle(LBLOC)

例

SELECT LBLOC, LabLocTitle(LBLOC) FROM edc.baseline_labs

上記の例では、Baseline Labs フォームで選択したラボの場所の LAB ID (LBLOC)、ラボの場所のタイトルが返されます。

OverrideReason(LBOVRDNRLO), OverrideReason(LBOVRDNHRI)

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

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(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(Item)

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

CODEDSTATUS(item)

SELECT CODEDSTATUS(ae_term.ae)
FROM adverse_event

CODEDBY(item)

SELECT CODEDBY(ae_term.ae)
FROM adverse_event

LASTCODED(item)

SELECT LASTCODED(ae_term.ae)
FROM adverse_event

KEYMATCH(form_name, reference_object_name)

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

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

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

IFNULL(DATAITEM,<Return_Value>)`

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

CONCAT(DATAITEM, 'string')

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

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 ( 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 (DATAITEM1, DATAITEM2, ...)

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

LENGTH(DATAITEM)

SELECT LENGTH(Item1)
FROM Form1

TRIM(DATAITEM)

SELECT TRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

LTRIM(DATAITEM)

SELECT LTRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

RTRIM(DATAITEM)

SELECT RTRIM(Baseline_X-ray.Interpretation)
FROM Baseline_Exam

UPPER(DATAITEM)

SELECT UPPER(Item1)
FROM Form1

LOWER(DATAITEM)

SELECT LOWER(Item1)
FROM Form1

LEFT(DATAITEM,len)

SELECT LEFT(Creation_Criteria.Initials,1)
FROM Demographics

RIGHT(DATAITEM,len)

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

MID ( DATAITEM, start index, length )

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

REGEXP 'regular expression pattern'

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

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

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

SUBSTR (DATAITEM, start index, length)

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

ADDDATE ('date', days)

ADDDATE('date', INTERVAL value unit)

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

SUBDATE ('date', days)

SUBDATE('date', INTERVAL value unit)

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

TIMESTAMPDIFF(month, date, date) 

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

DATEDIFF('date', 'date')

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

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

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

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

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

SDTMDateFormat(<date>)

SELECT SDTMDateFormat(mh.condition_start_date) from EDC.medical_history

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

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

SELECT CURDATE()
FROM Demographics

SELECT NOW()
FROM Demographics

YEAR('date')

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

MONTH('date')

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

WEEK('date')

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

DAY('date')

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

HOUR('time')

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

MINUTE('time')

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

SECOND('time')

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

LAST_DAY('date'), LAST_DAY(DATAITEM)

SELECT LAST_DAY(@HDR.Event.Date)

Unknown(Date)

Unknown(edc.mh_condition_start_date)

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

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

CAST(item AS data_type) 
CAST (expression AS data_type

SELECT CAST(Exclusion_Criteria.Hypersensitivity_Constituents as CHAR)
FROM Demographics

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

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

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

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

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

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

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

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

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;