Data rules validate the records in your study datasets. They are written in PQL (Pinnacle Query Language), a custom language based on SQL with added shorthand that simplifies writing validation rules. PQL is used for rules with Target = Data only.

How a Data rule logic works

A PQL expression is evaluated against every record in the dataset being validated and describes which records fail. In the rule's Logic section (set the Language to PQL) you fill in two boxes, referred to here as the Query and the Filter:

Box (label in the UI)	Required?	What it does
Query ("Raise an issue when the following criteria is met")	Required	The condition that identifies a failing record. Records matching it become findings.
Filter ("Only execute for records that match the following criteria")	Optional	Limits which records the Query is checked against.

Note Logic only describes which records fail. The rest of the rule is set in the other fields on the Properties tab (Rule ID, Message, Description, and Message Type) and on the Assignments tab (which standards and versions the rule runs against).

The data you can reference

Reference	Meaning
Variable in the current dataset	A bare variable name, e.g. `AESER`, `VISITNUM`.
Generic / placeholder variable	A `--` prefix matches the domain prefix, e.g. `--SEQ`, `--TERM`. Useful for rules that apply across domains.
Data supplement variable	A variable P21 automatically merges onto every dataset (chiefly subject-level `DM` variables such as `RFSTDTC` and `ARMCD`, and Trial Summary `TS` parameters). Reference it as `DATASET.VARIABLE`, e.g. `DM.RFSTDTC`.

Note A dot-value such as DM.RFSTDTC only works for data supplement variables: the variables P21 automatically adds to every dataset (chiefly subject-level DM variables and TS parameters). It is not a general way to join another dataset. A reference to anything that isn't a data supplement (for example SUPPDM.QNAM, or any variable on a custom standard) won't resolve, and because no error is raised, the rule can pass without checking what you intended. The complete list is in the Data supplement variables section at the bottom of this article.

Expression types

PQL expressions fall into three types.

Basic expressions

Format: PRIMARY_VALUE COMPARATOR COMPARATIVE_VALUE (a variable, an operator, and the value it's compared to).

AESER = 'Y'

DOSE > 0

VISITNUM IS NOT NULL

IN expressions

Compare one or more values against several comparative values with IN / NOT IN. Comparative values can be a tuple of variables or a list of strings.

ACTARMCD NOT IN ('SCRNFAIL', 'NOTASSGN', 'UNPLAN', 'NOTTRT')

Compound expressions

Combine Basic or IN expressions with AND / OR.

SSSTRESC IN ('DEAD', '死亡') AND USUBJID IS NOT NULL

AESER = 'Y' AND AEOUT IS NULL

Value types

Type	Notes & example
Variable reference	Standard (`USUBJID`), generic with dashes (`--TERM`), or a data supplement variable (`DM.RFSTDTC`).
Tuple	Multiple values compared together: `(USUBJID, AESEQ)`. Shorthand: `(VISITNUM, VISIT, VISITDY) IS NOT NULL` expands to each variable being not null.
List	Comma-separated strings in parentheses: `('SCRNFAIL', 'NOTASSGN')`.
String	Text in single quotes: `'Y'`, `'死亡'`.
Number	Numeric values used in comparisons: `DOSE > 0`.
NULL	Absence of a value: `--STRF IS NOT NULL`.

Comparators and operators

Valid comparators: <, <=, >, >=, =, !=, <>, IS, IS NOT. (!= and <> are equivalent; IS/IS NOT are used with NULL.) Membership operators: IN, NOT IN.

Functions

Function	What it does & example
`DUPLICATE(...)`	Flags duplicate values. `DUPLICATE(USUBJID) GROUP(STUDYID)`
`MISMATCH(...)`	Flags values that should match within a group but don't. `MISMATCH(QLABEL) GROUP(QNAM)`
`REGEXP(...)`	Tests a value against a regular expression; matches when the whole value matches the pattern. Negate with `NOT REGEXP(...)`. `NOT REGEXP(--REPNUM, '^[1-9]\d*$')`

GROUP

A special function used with DUPLICATE and MISMATCH to group records (like SQL's GROUP BY). You can group by more than one variable by separating them with commas.

DUPLICATE(USUBJID) GROUP(STUDYID)

MISMATCH(--ELTM) GROUP(--TPT, --TPTREF, VISITNUM)

Using REGEXP

REGEXP(variable, 'pattern') is true when the variable matches the pattern, and NOT REGEXP(variable, 'pattern') is true when the variable does not match the pattern. A few things to know:

Whole-value match. REGEXP checks the value from start to end, not just part of it. For example, REGEXP(--ORRES, '[0-9]+') matches '123' but not '12 mg', because the whole value has to be digits. (Since the match already covers the whole value, you don't need ^ or $ anchors.) To match a value that merely contains your text, add .* to both ends: REGEXP(VISIT, '.*SCREEN.*') matches any VISIT that contains SCREEN, such as 'UNSCHEDULED SCREEN'.
Blank values. A null or empty value never matches a pattern, so NOT REGEXP is true for blank rows. Add an IS NOT NULL check in the Filter if you don't want to flag those.
Backslashes. Write backslashes directly, for example \d for a digit or \. for a literal dot (not \\d or \\.).

Flags

Flag	What it does & example
`NOCASE`	Placed before a comparator to make the comparison case-insensitive. `TESTR NOCASE <> TEENRL`
`ANY`	Placed before a tuple so the comparator matches if any variable meets the condition. `ANY(COGRPID, COREFID, VISIT) IS NULL`

Examples

#	What it checks	Query	Filter
1	Serious AEs must have an outcome	`AESER = 'Y' AND AEOUT IS NULL`	—
2	ARMCD must be a valid value	`ACTARMCD NOT IN ('SCRNFAIL', 'NOTASSGN', 'UNPLAN', 'NOTTRT')`	—
3	No duplicate subjects within a study	`DUPLICATE(USUBJID) GROUP(STUDYID)`	—
4	Reported result must be a positive integer	`NOT REGEXP(--REPNUM, '^[1-9]\d*$')`	`--ORRES IS NOT NULL`
5	Flag unscheduled visits	`REGEXP(VISIT, '.[Uu]nsched.')`	—
6	AEs must not start before the reference start date	`AESTDTC < DM.RFSTDTC`	`AESTDTC IS NOT NULL AND DM.RFSTDTC IS NOT NULL`
7	Dose must not be negative	`--DOSE < 0`	`--DOSE IS NOT NULL`
8	ARM is not 'Screen Failure' when ARMCD equals 'SCRNFAIL', or vice versa	`ARM NOCASE NOT IN ('Screen Failure', '筛选失败') OR ARMCD NOCASE <> 'SCRNFAIL'`	`ARMCD = 'SCRNFAIL' OR ARM NOCASE IN ('Screen Failure', '筛选失败')`

Dates in ISO 8601 format compare correctly as text, so date variables can be compared with <, >, and the other operators (as in Example 6).

Data supplement variables

These are the variables a dot-value reference can resolve to. The set depends on the standard type, and they are not available on custom standards. Prefixes like DSMAX/DSMIN indicate which record is used when a subject has several: MAX takes the latest/largest value, MIN the earliest/smallest.

SDTM

Reference	Description
`DM.USUBJID`	Unique subject identifier
`DM.ACTARM`	Description of actual arm
`DM.ACTARMCD`	Actual arm code
`DM.ARM`	Description of planned arm
`DM.ARMCD`	Planned arm code
`DM.ARMNRS`	Reason arm and/or actual arm is null
`DM.DTHDTC`	Date/time of death
`DM.DTHFL`	Subject death flag
`DM.RFSTDTC`	Subject reference start date/time
`DM.RFENDTC`	Subject reference end date/time
`DM.RFXSTDTC`	Date/time of first study treatment
`DM.RFXENDTC`	Date/time of last study treatment
`DM.RFICDTC`	Date/time of informed consent
`DM.RFPENDTC`	Date/time of end of participation

SEND

Reference	Description
`DM.USUBJID`	Unique subject identifier
`DM.ACTARM`	Description of actual arm
`DM.ACTARMCD`	Actual arm code
`DM.ARM`	Description of planned arm
`DM.ARMCD`	Planned arm code
`DM.SETCD`	Set code
`DM.RFSTDTC`	Subject reference start date/time
`DM.RFENDTC`	Subject reference end date/time
`DM.RFXSTDTC`	Date/time of first study treatment
`DM.RFXENDTC`	Date/time of last study treatment
`DM.RFICDTC`	Date/time of informed consent
`DM.RFPENDTC`	Date/time of end of participation
`DM.RFCSTDTC`	Subject reference date/time (verify)
`DM.RFCENDTC`	Subject reference date/time (verify)

ADaM

Reference	Description
`ADSL.ARM`	Description of planned arm (from ADSL)
`ADSL.TRT01P`	Planned treatment for period 01
`DM.SUBJID`	Subject identifier for the study
`DM.STUDYID`	Study identifier
`DM.SITEID`	Study site identifier
`DM.ACTARM`	Description of actual arm
`DM.ARM`	Description of planned arm
`DM.ARMNRS`	Reason arm and/or actual arm is null
`DM.AGE`	Age
`DM.AGEU`	Age units
`DM.SEX`	Sex
`DM.RACE`	Race
`DM.ETHNIC`	Ethnicity
`DM.RFSTDTC`	Subject reference start date/time
`DM.RFENDTC`	Subject reference end date/time

Writing rules for the Data target