Counting no rows

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 9:11 am

My reject counter isn't returning a count if there are no rejected rows. Ideally I'd like the reject report to say there are zero. The only way I can see of doing it is to feed in a dummy error record into a link collector prior to the aggregator, then subtract 1 from the count in a transformer after the aggregator. Seems like a lot of effort on every rejection report though. Any other suggestions?

chulett · Post by **chulett** » Mon Mar 10, 2008 9:17 am

Perhaps it would help to explain how your current 'reject counter' is implemented?

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 9:23 am

It's just an aggregator stage that counts all the rows going into it. The reject output is split into this aggregator and a sequential file.

kcbland · Post by **kcbland** » Mon Mar 10, 2008 9:30 am

Ahhh, you need at least 1 row to generate the 0 answer. How about sending rows to the aggregator with a value and don't use a COUNT function but a SUM function? Always send a 0 value to the InvalidRows_Count link when @INROWNUM=1 (unless it's invalid) otherwise always send 1.

The problem will be that when your primary input stream is 0 rows you'll never get an output again.

Pierre · Post by **Pierre** » Mon Mar 10, 2008 9:32 am

You can use DSGetLinkInfo(...,DSJ.GETLINKROWCOUNT) If your job is launched by a Control Job.

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 9:40 am

Nice. Might use that. We can probably live with an empty report if there's no source data at all, that shouldn't happen.

sachin1 · Post by **sachin1** » Mon Mar 10, 2008 9:43 am

please let us know what are you using in transformer to derive the count of rows processed like, @OUTROWNUM or DSLinkRowCount or anything similar.

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 9:51 am

There isn't anything in the transformer that counts anything. The counting is done in the aggregator.

sachin1 · Post by **sachin1** » Mon Mar 10, 2008 9:55 am

i wanted to know what is the derivation for a column in transformer, for which you are taking a count in aggregator.

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 10:05 am

Oh, I see. The output link has three columns:

TYPE = fixed text describing the type of record, e.g. "Purchase Orders"
REPORT = fixed text categorising the rows, e.g. "Invalid Rows"
KEY = key value of record

The aggregator passes through the first two columns and counts the last one. Implementing Ken's suggestion would replace this KEY column with a numeric value that is 1 for a rejected row, or 0 for a non-reject row which only needs to be written out once for example when @INROWNUM=1. So the constraint would be something like "svError OR @INROWNUM=1", and the derivation would be "IF svError THEN 1 ELSE 0".

kcbland · Post by **kcbland** » Mon Mar 10, 2008 10:19 am

I suggested using the method of SUMming rather than COUNTing. You can send all rows to a reject inspection link and thru an aggregator. If the row is an issue, you set the value to 1. If the row is not an issue, you set the value to 0. Rather than counting explicit rows, you can sum a condition. You'll always get an output row from the Aggregator provided at least 1 row goes to it. I'm saying send the first, or all, just use SUM logic rather than COUNT.

PhilHibbs · Post by **PhilHibbs** » Mon Mar 10, 2008 10:26 am

Yes, I think that's the same as what I wrote.