Use logical and set operators

Logical operators

Logical operators act on individual rows of source data tables. You can use the following logical operators between conditions:

• AND—Find cases for which both conditions occurred simultaneously.
• OR—Find cases for which either of the conditions occurred.

You can use the logical operator NOT to negate a condition. (You cannot use NOT alone to connect conditions.)

Set operators

Set operators act on sets of cases that are retrieved by the conditions on each side of the set operator. You can use the following set operators between conditions:

• INTERSECT—Find cases that are in both sets.
• UNION—Find cases that are in either set.
• MINUS—Find cases that are in the set to the left of the operator, and subtract from that list the cases that are in the set to the right of the operator.

Sometimes it is possible to specify the same query using either logical operators or set operators. It is preferable to use logical operators because they are more efficient.

Operator priority

When the application interprets the query expression, it applies operators in the following order:

• NOT
• AND
• OR
• INTERSECT, UNION, MINUS (same priority)

The only restriction on how logical and set operators can interact is that logical operators cannot act on the results of set operators. For example, the following query is invalid: 1 AND (2 INTERSECT 3).

You can use parentheses to change the way in which a query expression is interpreted. If you do not use parentheses explicitly, the query is interpreted as if there are parentheses, based on the default order of operators.

For example, suppose the query is:

1 AND 2 OR 3 INTERSECT 4

With no supplied parentheses, the application reads the query as:

((1 AND 2) OR 3) INTERSECT 4

If you use parentheses, the meaning of the query is different:

1 AND (2 OR 3) INTERSECT 4

Examples

You specify the following conditions:

In the following examples, the query logic does not always refer to all three conditions. When you create a query within the Oracle Empirica Signal application, the query logic must reference all conditions in the query.

Note:

A condition can refer to multiple values. For example, you can include the DRUG variable once and select Thiamine and Niacin as one condition. The example includes the DRUG variable twice to illustrate complex logic.

If the source data includes cases A through G with the following data:

CASE_ID	DRUG	DOSE
A	Thiamine	25
A	Niacin	15
B	Thiamine	15
B	Niacin	25
C	Calcium	15
D	Thiamine	15
E	Thiamine	25
F	Niacin	25
G	Calcium	25

1 AND 3

The query retrieves cases with rows in which Thiamine occurred and DOSE >= 20 for that row:

A

E

1 OR 3

The query retrieves cases with rows in which either Thiamine occurred or DOSE >= 20 for any DRUG:

A

B

C

D

E

F

G

If you use OR between variables from different tables, only cases that are in each of those tables are retrieved. For example, if a query specifies that Death Date from the DEMO table is within a specified range OR Outcome from the OUTCOME table is Died OR PT from the REACTION table is Death, the application retrieves only cases that meet the criteria and are in all three tables. You can use set operators instead of logical operators. For example, replace OR with UNION in the query to find DEMO table cases with Death Date within the range plus OUTCOME table cases where the Outcome is Died plus REACTION table cases where the PT is Death.

1 AND 2

No cases are retrieved because no one row for a case can have both Thiamine and Niacin for a DRUG.

You can use the set operator INTERSECT if you want to find cases with both Thiamine and Niacin. See the 1 INTERSECT 2 example.

1 AND 2 OR 3

The query retrieves cases with rows in which Thiamine occurred and, for that row, Niacin occurred (not possible, because a row can have only one DRUG), or DOSE >= 20 for any DRUG:

A

B

E

F

G

1 AND 2 OR 3

The query retrieves cases with rows in which Thiamine occurred and, for the same row, either Niacin occurred (not possible, since a row can have only one DRUG) or DOSE >= 20:

A

E

1 AND NOT 3

The query retrieves cases with rows in which Thiamine occurred and DOSE is not >= 20 for that row:

B

D

NOT 1 AND 3

The query retrieves cases with rows in which Thiamine did not occur and DOSE >= 20:

B

F

G

NOT (1 AND 3)

The query retrieves cases with rows in which Thiamine with DOSE => 20 did not occur:

A

B

C

D

F

G

NOT 1 AND NOT 3

The query retrieves cases with rows in which Thiamine did not occur and, for the DRUG that occurred, DOSE is not >= 20:

A

C

1 INTERSECT 2

The query finds the intersection of the two sets:

A

B

1 INTERSECT 3

The query finds the intersection of the two sets:

A

B

E

Note that for case B, there is no occurrence of Thiamine where DOSE >= 20, but case B was in the set created by Condition 3 as well as the set created by condition 1, so the query retrieves it.

1 UNION 2

The query finds the union of the two sets:

A

B

D

E

F

3 MINUS 1

The query finds the cases that remain when cases in set 1 are removed from set 3:

F

G

1 INTERSECT 2 AND 3

The query finds the following case:

B

1 UNION 2 AND 3

The query finds the following cases:

A

B

D

E

F

Null values

If you check Include Null values for a condition, the condition finds cases with rows for which the condition is true or the variable referenced by the condition is null. If you precede the condition with NOT, the condition finds cases with rows for which the condition is not true or the variable referenced by the condition is not null.

For example, suppose that the source data has four null values for DOSE (indicated by dashes (-) in the following table):

CASE_ID	DRUG	DOSE
A	Thiamine	-
A	Niacin	15
B	Thiamine	-
B	Niacin	25
C	Calcium	-
D	Thiamine	15
E	Thiamine	25
F	Niacin	-
G	Calcium	25

If you specify the condition DOSE >= 20 and check Include Null values, the application finds the following cases:

A

B

C

E

F

G

When you specify that values for a query should include nulls and then you precede the condition with the NOT operator, the query retrieves cases with rows for which the condition is not true and the variable used in the condition is not null.

If you specify NOT 3 (meaning not condition 3, which is DOSE >= 20) and you have checked Include Null values, the following cases are found:

A

D