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.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' |
You can use the % wildcard character 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
Parent topic: Work with tables