Condition operators

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
(only with string database fields, e.g., CHAR, VARCHAR)

Comparison of field values with a variable string
The following placeholders are permitted:
* No or any number of characters
? Any single character
\ Masking character for searching for
placeholders or masking characters in the
form: \\ or \* or \?

Example:

<condition fieldname="OBJECTID"
logicaloperator="like">
<value>*10?0\\20?0*</value>
</condition>

The search is performed for values such as
5551050\20106667 or 1080\204044 but not 34510550\2030*

char_
creationtimestamp

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
(-begintime) or -enddate (-endtime) command line parameters [see chapter Source database specific arguments].
Multiple fields are separated by the character combination #-#. For each field, the time stamp format is specified in the value XML element. This format contains the values in the corresponding source database field.

Example:

<condition fieldname="VC_DATE#-#VC_TIME"
logicaloperator="char_creationtimestamp">
<value>yyyyMMdd</value>
<value>HHmmss</value>
</condition>

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_
creationtimestamp

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
(-begintime) or -enddate (-endtime) command line parameters [see chapter Source database specific arguments].
Multiple fields are separated by the character combination #-#. For each field to be extracted, the value XML elements are used to specify the one of the three time data types corresponding to the source database fields. Only the following data types in the specified combinations are permitted: DATE/TIME or TIME/DATE or TIMESTAMP or DATE

Example:

<condition fieldname="CHG_DATE#-#CHG_TIME"
logicaloperator="date_creationtimestamp">
<value>DATE</value>
<value>TIME</value>
</condition>

valueconstraint

Specifies a source database system field with integer values that are used for delimiting the data range to be extracted with the -valueconstraint
command line parameter [see chapter Source database specific arguments].
The field to be extracted must be of an integer data type.

Example:

<condition fieldname="INTEGERFIELD"
logicaloperator="valueconstraint"/>

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.