Common operators
The following comparison operators are supported by the condition and doccondition XML elements:
Operator |
Description |
---|---|
eq |
Field content is equal to the specified value. |
neq |
Field content is not equal to the specified value. |
in |
Field content is equal to a specified value from a set of values. |
notin |
Field content is not equal to a specified value from a set of values. |
is_null |
Checks whether the field content is NULL |
is_not_null |
Checks whether the field content is not equal to NULL |
Condition operators
Operator |
Description |
---|---|
num_gt |
Field content is greater than specified value. |
num_geq |
Field content is greater than or equal to the specified value. |
num_lt |
Field content is less than specified value. |
num_leq |
Field content is less than or equal to the specified value. |
num_eq |
Field content is equal to the specified value. |
num_neq |
Field content is not equal to the specified value. |
num_in |
Field content is equal to a specified value from a set of values. |
num_notin |
Field content is not equal to a specified value from a set of values. |
timestamp_eq |
Checks whether the time stamp of the field corresponds to the specified comparison value (value). The comparison value must be specified in dd.MM.yyyy HH:mm:ss format – this also applies to all other timestamp_* operators. |
timestamp_geq |
Checks whether the time stamp of the field is greater than or equal to the comparison value. |
timestamp_gt |
Checks whether the time stamp of the field is greater than the comparison value. |
timestamp_leq |
Checks whether the time stamp of the field is less than or equal to the comparison value. |
timestamp_lt |
Checks whether the time stamp of the field is less than the comparison value. |
time_eq |
Checks the field's time for equality. The comparison value must be specified in HH:mm:ss format – this also applies to all other time_* operators. |
time_geq |
Checks whether the time of the field is greater than or equal to the comparison value |
time_gt |
Checks whether the time of the field is greater than the comparison value |
time_leq |
Checks whether the time of the field is less than or equal to the comparison value |
time_lt |
Checks whether the time of the field is less than the comparison value |
date_eq |
Checks whether the date of the field is equal to the comparison value. The comparison value must be specified in dd.MM.yyyy format – this also applies to all other date_* operators. |
date_geq |
Checks whether the date of the field is greater than or equal to the comparison value |
date_gt |
Checks whether the date of the field is greater than the comparison value |
date_leq |
Checks whether the date of the field is less than or equal to the comparison value |
date_lt |
Checks whether the date of the field is less than the comparison value |
like |
Comparison of field values with a variable string Example: <condition fieldname="OBJECTID" The search is performed for values such as |
char_ |
Extracts time stamps (date and time) from source database fields of the CHAR/VARCHAR type. Their values form the basis for restricting the data range to be extracted with the -begindate Example: <condition fieldname="VC_DATE#-#VC_TIME" When using the char_creationtimestamp operator, be aware of possible effects of certain source database field time formats on data sorting and extraction. The database fields in a time format are extracted in alphabetical order. For example, if the date field values in the database are saved in ddMMyyyy format, the date 23021999 would be extracted when extracting the time period between 15.01.2000 - 31.12.2000 because in alphabetical terms, this date lies between the start and end date. However, the date 09122000 is not in this range because it is interpreted as earlier than the start date. |
date_ |
Time stamps (date and time) are extracted from source database fields with times that are of a database system-dependent time data type. Their values form the basis for restricting the data range to be extracted with the -begindate Example: <condition fieldname="CHG_DATE#-#CHG_TIME" |
valueconstraint |
Specifies a source database system field with integer values that are used for delimiting the data range to be extracted with the -valueconstraint Example: <condition fieldname="INTEGERFIELD" |
When using the timestamp_*, time_* and date_* operators from the above table, you need to know which data type in which format the value extracted from the corresponding database system (Oracle, IBM DB2, MS SQL Server) is based on.
The following tables provide an overview of the different DB data types and DB formats and show some examples of formats generated in the XML output file.
Oracle/TIMESTAMP data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 04:02:36 |
07.05.2005 04:02:36 |
- |
07.05.2005 |
07.05.2005 12:25:11 |
Time = Creation time of database field value |
04:02:36 |
21.12.2005 04:02:36 |
Date = Creation date of database field value |
Oracle/DATE data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 04:02:36 |
07.05.2005 04:02:36 |
- |
07.05.2005 |
07.05.2005 12:25:11 |
Time = Creation time of database field value |
04:02:36 |
21.12.2005 04:02:36 |
Date = Creation date of database field value |
The Oracle DATE data type saves time stamp values exclusively in dd.MM.yyyy HH:mm:ss format. To extract the required data from an Oracle database, you need to configure the extract conditions accordingly when using date_* operators.
Example
You want to extract all data records with the date 16.09.2004, regardless of the time. If you specify the following condition in the table configuration:
<condition fieldname="TACT_TDATE" logicaloperator="date_eq">
<value>16.09.2004</value>
</condition>
only data records with the 16.09.2004 00:00:00 time stamp are extracted. To ensure that all records of the specified date are extracted, you need to re-write the condition as follows:
<booleancondition logicaloperator="AND">
<condition fieldname="TACT_TDATE"
logicaloperator="date_geq">
<value>16.09.2004</value>
</condition>
<condition fieldname="TACT_TDATE"
logicaloperator="date_lt">
<value>17.09.2004</value>
</condition>
</booleancondition>
With the specified condition, all data records with the date 16.09.2004 and any time are extracted.
IBM DB2/TIMESTAMP data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 04:02:36 |
07.05.2005 04:02:36 |
- |
IBM DB2/DATE data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 |
07.05.2005 |
- |
IBM DB2/TIME data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
04:02:36 |
04:02:36 |
- |
MS SQL Server/DATETIME data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 04:02:36 |
07.05.2005 04:02:36 |
- |
07.05.2005 |
07.05.2005 00:00:00 |
Time is always 00:00:00 |
04:02:36 |
01.01.1900 04:02:36 |
Date is always 01:01:1900 |
MS SQL Server/SMALLDATETIME data type
Value format written to database (example) |
Resulting format in XML output file (example) |
Note |
---|---|---|
07.05.2005 04:02:36 |
07.05.2005 04:02:00 |
Time only correct to the minute |
07.05.2005 |
07.05.2005 00:00:00 |
Time is always 00:00:00 |
04:02:36 |
01.01.1900 04:02:00 |
Date is always 01.01.1900 and time is correct to the minute |
doccondition operators
Operator |
Description |
---|---|
exists |
Field exists. |
notexists |
Field does not exist. |