In this section Hide
At certain places in the application, you can specify a SQL WHERE clause to restrict rows of a table or report. When specifying a SQL WHERE clause, do not enter the word WHERE. The application 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:
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.
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' |
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 |
> |
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 |
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 2000-01-17. You specify:
RCVD_DATE > TO_DATE('2000-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.
To select table columns (variables) to include in the WHERE clause, click Show Columns in the Columns and Rows window.
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 drug 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.
The Empirica Signal application 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 |
|
Note:
The following are the caveats of the SQL functions: