Not able to reject rows from transformer

[tanaya.deshpande@tieto.co]

I have defined a constraint in the Transformer

len(ProductID) <> 0 (simple)

I have a reject link pointing to reject file ..but the reject file is not made.

the constraint page in my transformer does not have Reject Rows tab
is this the reason why ..the reject file is not made

though I can catch the records not satisfying the constraint by OTHERWISE constraint..

BUt what about the reject link and Reject file

[abhilashnair]

Whats the structure of your job?

[jwiles]

The reject link captures input records which do not get written to an output link due to either a write failure or the failure of an expression evaluation. Typically you see records rejected because of Nulls which were not handled properly, resulting in an expression evaluation failure.

Using the otherwise clause (as you did), or specifically coding a constraint, is the correct way to force a "reject" of records in the transformer.

Regards,

[tanaya.deshpande@tieto.co]

ok so that means only records having nulls can be rejected to a reject stream ..and not other invalid values (as per the business requirement) ??

also there is no Reject tab in the constraint page of the transformer i am using ..what can be the reason ..??

The structure of my job is simple a sequential stage for source file then a transformer where I am checking for nulls ..and again a sequential file for output as well as Reject file...

[MarkB]

Is the only thing you are doing in the transformer checking for nulls?

If all you are doing is taking a sequential file, checking one (or more) fields for nulls, then outputting to your target, it would be easy to simply define those fields in the sequential file source as non-nullable, hang a reject link off of the sequential source, and change reject mode on the sequential source to output. That will capture the rows with nulls.

[jwiles]

The reject output of a transformer stage is more of an internal reject--rejected by the transformer itself. The NULL rejects were an example and occur under certain conditions. If YOU wish to reject rows with null values (or any other values), you should write your own logic to do so. Also, the reject link is strictly a copy of the input link...same metadata. You can't add additional columns, such as a reject reason code/message/id/etc., to it.

You can certainly perform your own reject processing (data validation, in other words!) but you cannot force them to go down the reject link. You would create a regular output link and use constraints to determine your rejects (using Otherwise or your own logic). There is nothing to stop you from checking for nulls or any other condition and sending those down that link, with any additional data you wish to provide. You just cannot force anything down the reject link and therefore there is no reject link tab/row in the constraints.

Regards,

[tanaya.deshpande@tieto.co]

Ok ..Mark

I did as u suggested produced a reject link from the sequential file stage
and put the reject mode to output..but still its not rejecting the nulls..

May be the source file is made in wrong way

this is the data in my file...

SalesOrderNumber,ProductID,OrderDate,PalanedDeliveryDate,ActualDeliveryDate,CountryID,Quantity,ProductionCost,OtherCostSales,Cost,VAT
,ABC,20110222,20100228,20100202,IND,2.00,3300.00,500.00,3400.00,35.00
10102,,20110222,20100228,20100202,IND,2.00,3300.00,500.00,3400.00,35.00
10102,ABC,20110222,20100228,20100202,,2.00,3300.00,500.00,3400.00,35.00

Now the nulls here should be rejected via the output link from the sequential file..All the columns are nullable No ..it doesent work even if I set it to nullable yes..

what is wrong with the data???

[jwiles]

Nothing's wrong with the data. With the fields defined as non-nullable, they don't get imported as NULLS but rather as empty strings. Perfectly ok and won't be rejected by the operator if the columns are defined as VarChar. But, if they're defined as CHAR instead....it MIGHT reject, but not necessarily for the reason you expect: "Column_Import_3,0: Field "ProductID" delimiter not seen, at offset: 9". ProductID was defined as Char(3) non-nullable.

The better way to reliably reject these records is through logic in a transformer. Gives you more control over when and how to reject at the column level. The reject link out of SeqFile will capture any record the operator was unable to import, no matter the reason, and no reason information is provided with the rejected record.

Regards,

[ray.wurlod]

The Reject link handles rows that cause an internal exception. The Otherwise/Log link handles rows that are not processed by any preceding output link.