NLS READ FIXEDWIDTH

just4geeks · Post by **just4geeks** » Sat Mar 17, 2007 10:23 am

ray.wurlod wrote:Add a reject-handling link to the Sequential File stage that is reading the file to capture any row that fails to satisfy the main output link's metadata

I added a reject link in a transformer and directed the reject links into a Seq file stage with comma delimited style. The reject file had the same column def as the normal link. And yet, the job aborted with the nls_put_fixedwidth() error.

Code: Select all

extraction_job..SF_DataFile.link_op_DataFile: nls_put_fixedwidth() - data for column STORE exceeds column width (5), row 113 (approx), data = 00188

Also SFData is a SEQ file stage.

Is there a better way to implement reject links? Please let me know. Thanks for your time.

chulett · Post by **chulett** » Sat Mar 17, 2007 10:44 am

You didn't manage to send all of the problem children down only the reject link. How did you configure this 'reject' link? Did anything make it into the reject file before your 50 limit triggered the abort? (Which is what I assume happened) Unless you will generate bazillions of errors, you might want to consider running this once with unlimited warnings to see how many problem children actually live in your data.

If anything was rejected, were you able to determine why they were rejected?

just4geeks · Post by **just4geeks** » Sat Mar 17, 2007 10:51 am

chulett wrote:How did you configure this 'reject' link? Did anything make it into the reject file before your 50 limit triggered the abort?

I ran the job and configured it to abort after 1 warning. I just wanted to take a look at the problem record. However, nothing made it do the reject file. I configured this reject link using a transformer and checking 'reject link' inside the transformer stage constraints window.

just4geeks · Post by **just4geeks** » Sat Mar 17, 2007 10:59 am

chulett wrote:Does the problem get solved if you change your derivation just before the Sequential File stage to substring the value? In essence:
Code: Select all
Link.Retail[1,5]
Of course, use your real link and column name there.

Yeah, it worked without a hitch. Thanks Craig. :D

I have some time to kill, let me work on finding the cause of the problem. I am now running the job with a reject link.

chulett · Post by **chulett** » Sat Mar 17, 2007 11:16 am

just4geeks wrote:I ran the job and configured it to abort after 1 warning. I just wanted to take a look at the problem record. However, nothing made it do the reject file. I configured this reject link using a transformer and checking 'reject link' inside the transformer stage constraints window.

Hmmm... I know that would work for a database stage, it would automagically take any records spit back. Not sure that works with a Sequential File as I've honestly never tried it. You might want to add an actual constraint expression in your first link in addition to the reject check box for the second so you have explicit control over what goes where. For example, in your main file writing link, this constraint:

Code: Select all

Len(Link.RetailColumn) <= 5

That way only records where the data will 'fit' go to the real file and those that don't get caught in the 'Reject' link. You could technically do that for every problem field and just 'and' them together in the constraint.

And just to be anal, your 'Reject' link is the last link coming from the transformer when you check the 'Link Ordering', yes? You are aware that that is... important, yes?

just4geeks · Post by **just4geeks** » Sat Mar 17, 2007 11:35 am

chulett wrote:your 'Reject' link is the last link coming from the transformer when you check the 'Link Ordering', yes? You are aware that that is... important, yes?

Yes, my reject link is the last link in the 'link list'. But I wasn't aware that it is important. I will remember that. Thanks again Craig....

chulett · Post by **chulett** » Sat Mar 17, 2007 11:48 am

It's not so much that 'last' is important, but that the ordering of the reject link is important - especially if all you've done is check the Reject box.

That's because the rules for its use are positional - it means I want data to go down this reject link if it hasn't gone down any other link yet. An example, five links with constraints based on color:

1) A link for all 'blue' records.
2) A link for all 'green' records.
3) A link for all 'red' records.
4) Reject link with no constrain expression.
5) A link for all 'purple' records.

Now, a yellow record will look like everything worked because it will go down only the reject link. Any blue, green or red ones will look fine as well. However, what happens when a puple record hits the mixer? Yes, it does go out the 5th link but it also goes out the 4th link because it did not go down any of the three links before it got to number 4.

It can get more complicated than that, but something to be aware of.

just4geeks · Post by **just4geeks** » Sat Mar 17, 2007 11:59 am

chulett wrote:It's not so much that 'last' is important, but that the ordering of the reject link is important - especially if all you've done is check the Reject box..............

Thanks Craig for taking the time to type it out. That is good information....