Issue while handling REJECT records

[DSFreddie]

Hi All,

I am reading a reject file in the same job to perform some transformations. All the fields are VARCHAR & Nullable Yes. While the data passes from Reject file to next transformer, a few records are getting dropped due to NULL handling issue. I am already handling NULL using the below condition.

If Trim(Field.A) = '' or IsNull(Field.A) Then SetNull() Else (TrimLeadingTrailing (Field.A))

I then tried assigning the below in the Reject file,
Field_Defaults=Null Field Value =NULL
Now it is rejecting all the records.

Reject_Mode is set as : Output

Any thoughts will be very helpful.

Thanks
Freddie

[SURA]

Hi All,

I am reading a reject file in the same job to perform some transformations. All the fields are VARCHAR & Nullable Yes.

Can you read the reject file in the same job?

While write the data into the file did all the records captured?

Make the option Null Field Value = ''

DS User

[ray.wurlod]

You must handle the nulls before essaying the use of any other function in the Transformer stage.

[jwiles]

Rewrite your logic as:

Code: Select all

if IsNull(Field.A) then SetNull() else if Trim(Field.A) = '' then SetNull() else TrimLeadingTrailing(Field.A)

When you are including null-handling logic, handle the nulls first before you execute other logic which will examine the value of the column.

Regards,

[jwiles]

Dang, beat me to it Ray!

[DSFreddie]

Hi All,

Thanks for the quick responses. I modified the Null handling as specified by Jiles in the job which creates the input file to the Lookup Stage(this stage generates the rejected records).

I could see that there are 4000 record getting written to the reject file, but Zero records is getting passed from the Reject file to the next transformer.

Any thoughts ?

Thanks,
Freddie

[jwiles]

You now have me confused about your job: Is there one job or two? Your description of your process is not clear.

Let's concentrate on the transformer you modified with the updated null-handling logic: Is it now working correctly? Are you no longer rejecting records from it because of null values? That was the original problem you described.

[DSFreddie]

Hi James,

The flow is like below,

********* Reference file
**************|
Input File ----> lookup
**************|
***********Reject file
**************|
**********Transformer--->remove dup---> few other logic

The issue is with the Reject File/Transformer part(No records is passing beyond Reject File). The null handling is already done while creating this input file in the previous job. Hope the flow is clear now.

Thanks,
Freddie

[jwiles]

Code: Select all

********* Reference file 
**************| 
Input File ----> lookup 
**************|     (????what type of link: stream or reject????)    
***********Reject file (?????sequential file stage?????)
**************|     (?????this is a reject link?????) 
**********Transformer--->remove dup---> few other logic 

The output from the lookup stage flows into a sequential file stage ("Reject file"), correct? The sequential file stage has a reject link which flows into the transformer?

If the output from the Reject file stage (sequential file?) is a reject link--all you could have if it is receiving data from the lookup stage--then the transformer would only receive records rejected by the Reject file stage (records it can't correctly write to the file).

Regards,

[chulett]

I was wondering how it allowed that job design but having the last link as yet another reject link is the only thing that makes sense. PX is not like Server in that it does not allow input and output links from the same target stage. If you want to 'process' that reject file, you'll need to use another job.

[DSFreddie]

Thanks James & Chulett.

James, to answer your question, yeah the Reject file is Sequential file. both the links from lookup (flowing downwards) are Reject links.

Are you saying that we cannot read the records from Reject file & process it within the same job itself ? If that is the case, I can develop a new job to read the reject file & do further processing.

Thanks,
Freddie

[jwiles]

That is correct. You can either read a file or write a file, but cannot do both to the same file within the same job. You could process the reject records before you write them to the file, or copy them--one stream going to the file and the other going into the processing logic, or as you mentioned just create a third job to process the file.

Regards,

[Satwika]

Hi ,

jwiles extension : -- So The job flows as below :

********* Reference file
**************|
Input File ----> lookup
**************|
*********** Copy ---> Transformer --> remove dup --> Few other logic
**************|
***********Reject file

Normally Sequential File stage does not support any output stream (non reject links) when it has input links.

Or You can built this in another job using the reject file to perform your desired transactions.

Regards
Satwika

[SURA]

In PX you cant use IN and OUT from the same Sequential file stage. See my first comment for your post .

If the file fails to write any data then only that data will pass as the output. That is the reason why you are not getting any data into the TFM.

DS User