Specify a SQL WHERE clause

At certain places in Oracle Empirica Signal, you can specify a SQL WHERE clause to restrict rows of a table or a report.

Note:

When specifying a SQL WHERE Clause, you can click Show Columns to display the Select Table Columns page listing variables (columns) that you can include in the WHERE clause. To insert a variable into the WHERE clause, click the column name.

At certain places in Oracle Empirica Signal, you can specify a SQL WHERE clause to restrict rows of a table or a report.

When specifying a SQL WHERE clause, do not enter the word WHERE. Oracle Empirica Signal adds WHERE internally to the start of the condition that you enter. For example, to view all results for which EBGM is greater than 5, type EBGM > 5.

To connect conditions, use AND or OR. For example:

• N > 5 AND EBGM > 8
• EBGM > 5 OR EBGMDIF > 6

If you provide a SQL expression with incorrect syntax or unsupported SQL functions, an error message appears at the top of the page when you save the expression. Common errors are a wrong or misspelled column name, and unclosed or mismatched parentheses.

The way in which a variable is stored in the Oracle database determines how you can search for it.

Text variables

If a variable is stored as a text field in the Oracle database, you must enclose the text string in single quotes. Capitalization within the quoted string must match the text string in the database exactly.

To find a value that includes a single quotation mark, or apostrophe, precede the quotation mark or apostrophe with another single quotation mark in the quoted string. For example, to find Bell's palsy for the PT variable, type PT like 'Bell''s palsy'.

You can use any valid SQL operators, including the following commonly used operators:

Operator	Meaning	Example
LIKE	matches	SUBSET LIKE 'F'
NOT	is not the condition that follows	SUBSET NOT LIKE 'F'

% is a wildcard character that you can use in a text string after LIKE; it matches any characters in its position, as follows:

Type of Match	Example
Exact match	ITEM1 LIKE 'Asthma NOS'
Starts with specified string	ITEM1 LIKE 'As%'
Includes specified string	ITEM1 LIKE '%th%'
Ends with specified string	ITEM1 LIKE '%NOS'

Numeric variables

If a variable is stored as a number field in the Oracle database, you can use any valid SQL operators, including the following commonly used operators:

Operator	Meaning	Example
=	is equal to	EBGM = 3
!= or <>	is not equal to	EBGM != 3 EBGM <>3
>	is greater than	EBGM > 98.6
>=	is greater than or equal to	EBGM >= 98.6
<	is less than	EBGM < 8.5
<=	is less than or equal to	EBGM <= 8.5
BETWEEN	is between (includes both 6.5 and 8.0)	EBGM BETWEEN 6.5 AND 8.0

Date variables

If a date variable is stored as a text field in the Oracle database, you can search for it as you would search for any text string.

If a date variable is stored as a date field in the Oracle database, you can use the Oracle function TO_DATE to change a text string to an Oracle date. Then you can use the same operators as for numeric variables. For example, if you want to find RCVD_DATE dates later than 2019-01-17, you specify:

RCVD_DATE > TO_DATE('2019-01-17', 'yyyy-mm-dd', 'NLS_DATE_LANGUAGE = American')

If you do not specify a time, the time is considered to be midnight of the specified date.

Column names

To select table columns (variables) to include in the WHERE clause, from the Header Action menu () on the Products or Product-Event Combinations tables, click Columns. On the Columns dialog, enter the WHERE clause in the Where Clause text box. To copy an existing column into the WHERE clause, click the Show columns link.

When using a SQL WHERE clause to select criteria for run results that you are viewing or to filter a source table that you are viewing, avoid referring to the product or event variables or related hierarchy terms. The actual column names in the underlying table are not the same as the labels for fields on the Select Criteria page or as the column headers in the results table. The actual column names are ITEM1, ITEM2, etc.

Note:

The alphabetical order of prefixes defined in the configuration for the drug and event variables determines whether the variables are ITEM1 or ITEM2. For example, if the prefix for the drug variable is D and the prefix for the event variable is E, the drug variable is ITEM1 because D is first alphabetically.

SQL functions

Oracle Empirica Signal supports the following SQL functions in WHERE clauses:

acos	length	soundex
ascii	lower	sqrt
asciiStr	last_day	substr
asin	ln	tan
atan	log	tanh
atan2	lpad	to_date
BitAnd	ltrim	to_dsinterval1
Ceil	mod	to_multi_byte
Chr	months_between	to_number
Compose	new_time	to_single_byte
concat	next_day	to_timestamp
convert	power	to_timestamp_tz
cos	remainder	translate
cosh	replace	tz_offset
decompose	round	abs
dump	rpad	to_yminterval
exp	rtrim	add_months
floor	sign	upper
initcap	sin	vsize
from_tz	sinh	to_char
instr	-	-

The following are caveats of the SQL functions:

• Functions with no parameters are permitted (for example, sysdate)
• Functions with optional arguments (for example, to_date) require ALL arguments