Extract the first or last line in a sorting

The Java class to be used com.idsscheer.ppm.xmlextractortools.extractor.jdbc2ppm. Line break
ZSortWithInteger_jdbc2ppm sorts records in a data table by means of the specified sorting criterion, and either writes the first or last record to the system events to be generated. The class must be specified in the classtouse attribute of the table XML element.

Example

With this class, you can use an integer criterion (amount of items) to sort data records that are referenced via foreign key relations in the ORDER ITEM table. The sort criterion is the AMOUNT field. From the data sorted, the data record with the largest sorting criterion value (for ITEM AMOUNT_MAX) and the data record with the smallest sorting criterion value (for ITEM AMOUNT_MIN) is written to the generated system events, each with the corresponding item number. The following file extract illustrates the extraction configuration under the conditions described above:  

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE jdbc_tableconfiguration SYSTEM Line break

'jdbc_tableconfiguration.dtd'>

<jdbc_tableconfiguration>

<configuration name="SortWithInteger">

<docspec>

<doctable name="ORDER HEADER">

<pkfield name="NUMBER"/>

</doctable>

</docspec>

<table name="ITEM AMOUNT_MAX" Line break

tablename="ORDER ITEM" Line break

classtouse="com.idsscheer.ppm.xmlextractortools. Line break

extractor.jdbc2ppm. Line break

ZSortWithInteger_jdbc2ppm">

<parameter name="SORTCRITERION">

<value>AMOUNT</value>

</parameter>

<parameter name="USE">

<value>MAX</value>

</parameter>

<pkfield name="NUMBER" fktablename="ORDER HEADER" Line break

fkfieldname="NUMBER"/>

<fieldtoread name="ITEM"/>

<fieldtoread name="AMOUNT"/>

</table>

<table name="ITEM AMOUNT_MIN" Line break

tablename="ORDER ITEM" Line break

classtouse="com.idsscheer.ppm.xmlextractortools. Line break

extractor.jdbc2ppm. Line break

ZSortWithInteger_jdbc2ppm">

<parameter name="SORTCRITERION">

<value>AMOUNT</value>

</parameter>

<parameter name="USE">

<value>MIN</value>

</parameter>

<pkfield name="NUMBER" fktablename="ORDER HEADER" Line break

fkfieldname="NUMBER"/>

<fieldtoread name="ITEM"/>

<fieldtoread name="AMOUNT"/>

</table>

</configuration>

</jdbc_tableconfiguration>

A system event extracted with this configuration could look like this: 

<event>

<attribute type="ORDER HEADER NUMBER">

4711

</attribute>

<attribute type="ITEM AMOUNT_MAX-ITEM">

20

</attribute>

<attribute type="ITEM AMOUNT_MAX-AMOUNT">

100000

</attribute>

<attribute type="ITEM AMOUNT_MIN-ITEM">

50

</attribute>

<attribute type="ITEM AMOUNT_MIN-AMOUNT">

470

</attribute>

</event>

The sorting criterion (in this case AMOUNT) is automatically included in the system event specification.

The template for creating the shown configuration looks like this:

<table name="..." tablename="..." Line break

classtouse="com.idsscheer.ppm.xmlextractortools. Line break

extractor.jdbc2ppm. Line break

ZSortWithInteger_jdbc2ppm">

<parameter name="SORTCRITERION">

<value>...</value>

</parameter>

<parameter name="USE">

<value>...</value>

</parameter>

...

See chapter 

JDBC table configuration

...

</table>

The table below reiterates the most important configuration entries for the above table definition:

XML element/attribute

Value: Description

name

The specified name is added to the extracted source system attributes as a prefix

tablename
(optional)

Table from which the information is to be extracted. Default value: Value from name XML attribute

classtouse

com.idsscheer.ppm.xmlextractortools.
extractor. jdbc2ppm.ZSortWithInteger_jdbc2ppm:

Java class to be used

parameter name

 

SORTCRITERION

First parameter to be specified.

A single value XML element must be specified (field name of sorting criterion). The specified field may only contain integer values.

USE

Second parameter to be specified.

A single value XML element must be specified. Valid values:
MIN (the data record with the lowest integer value for the sorting criterion is selected)
MAX (the data record with the highest integer value for the sorting criterion is selected)

Either the first or last data record from the list generated based on the specified sorting criterion is selected to be extracted.

You should use this class for extracting the first or last line of a sorting sparingly, as the sorting and selection operations may result in a loss of performance and memory.