[resolved] RecordSet - more output rows than input rows!?

One Star

[resolved] RecordSet - more output rows than input rows!?

Hello,
i find something i don't understand:
I access two databases in a row. Thereby the query of the second database use the results from the first database query. I have 4209 output rows.
Now i use the tParseRecordSet to get the single records. Because this component filters half of my records, i use a second path, where i get all the other records.
To do that, i replicate the input and use a tFilterRow with the following instruction:
"((java.sql.ResultSet)row7.webpxeRecordSet).isAfterLast()==false"

When i add the output rows of the tParseRecordSet-Path and the output rows of the tFilterRow-Path, i get 4215 rows (but i just have 4209 input rows). When i compare the two ouput files (from the tParseRecordSet and the tFilterRow), i find no duplicates.
Can it be that the tParseRecordSet "create" new rows? Or is my tFilterRow component wrong? What else could it be?

Accepted Solutions
Seventeen Stars

Re: [resolved] RecordSet - more output rows than input rows!?

Every row floating through your tMysqlRow executes a query and this query returns a resultset with a unknown number of rows. The rows floating through the tParseRecordSet will pass this component as much as the record set it self has rows. Thats why it could happened, you will get more or less rows as output compared to the number of inout rows.
If you switch to the Java code view for the tParseRecordSet you would see something like this:
I assume the schema column containing the record set is called "rc" and row6 and row3 have both a common column called id.
ResultSet rset = (java.sql.ResultSet) row6.rc;
while (rset.next) {
row3.my_value = rset.getString("abc");
row3.id = row6.id;
......

For every input row6 the tParseRecordSet takes the ResultSet from the column configured to carry the ResultSet.
It iterated through the result set. If the resultset does not have rows for it self you will get no output rows in row3!
If every resultset would returns 2 rows, the number of rows in row3 would be double the number of rows in row3.
Dies it make it clear for you. The best thing is select in your job tParseRecordSet and switch to the Java code view. You will see the while loop with the next call to a result set.

All Replies
One Star

Re: [resolved] RecordSet - more output rows than input rows!?

So what do the 'invented' records contain?
Seventeen Stars

Re: [resolved] RecordSet - more output rows than input rows!?

Every row floating through your tMysqlRow executes a query and this query returns a resultset with a unknown number of rows. The rows floating through the tParseRecordSet will pass this component as much as the record set it self has rows. Thats why it could happened, you will get more or less rows as output compared to the number of inout rows.
If you switch to the Java code view for the tParseRecordSet you would see something like this:
I assume the schema column containing the record set is called "rc" and row6 and row3 have both a common column called id.
ResultSet rset = (java.sql.ResultSet) row6.rc;
while (rset.next) {
row3.my_value = rset.getString("abc");
row3.id = row6.id;
......

For every input row6 the tParseRecordSet takes the ResultSet from the column configured to carry the ResultSet.
It iterated through the result set. If the resultset does not have rows for it self you will get no output rows in row3!
If every resultset would returns 2 rows, the number of rows in row3 would be double the number of rows in row3.
Dies it make it clear for you. The best thing is select in your job tParseRecordSet and switch to the Java code view. You will see the while loop with the next call to a result set.
One Star

Re: [resolved] RecordSet - more output rows than input rows!?

Thanks for your detailed answer, that was a great help!