Record dropped during NULL handling

[divstands]

The job looks like:

Seq File1->Transformer1->Standardize->Transformer2->Seq File2

While converting the data from Tranformer2 to Seq File2, the job is giving the following error:

APT_CombinedOperatorController(1),0: Field 'Exterior_MXADDR' from input dataset '0' is NULL. Record dropped. [transform/tfmop_functions.C:147]


Other details:
In Tranformer2, i have used a derivation for Exterior_MXADDR which is :

If DSLink74.Exterior_MXADDR = "7777777777" or DSLink74.Exterior_MXADDR = "" then "SN" else NullToEmpty(DSLink74.Exterior_MXADDR)

For SeqFile2, format properties are:
NULL Field value: NULL
Quote: None
Delimiter: | ( same are the porperties for SeqFile1 too)


Hence, as far as i see, i have taken due care to handle the nulls, still because of NULLs in the field Exterior_MXADDR, the records are getting dropped. What can be the solution.

[divstands]

Additional Information:
In Transformer1 i have used the derivation(exact opposite of what i used in Transformer2):

If (TrimLeadingTrailing(DSLink48.pnumext) = "SN" or TrimLeadingTrailing(DSLink48.pnumext)="S/N") Then "" Else DSLink48.pnumext

which is working perfectly

[stuartjvnorton]

Additional Information:
In Transformer1 i have used the derivation(exact opposite of what i used in Transformer2):

If (TrimLeadingTrailing(DSLink48.pnumext) = "SN" or TrimLeadingTrailing(DSLink48.pnumext)="S/N") Then "" Else DSLink48.pnumext

which is working perfectly

A few things:

1 - Disable operator combination. Then you'll know what's going on.
2 - Set up default NULL handling for your output file.
3 - TrimLeadingTrailing on a NULL value will cause records to be dropped.
4 - Post this on the DataStage forum, where it belongs.

[divstands]

When i copied the same job to Server from parllel, it worked. HAve no reason why?

[vairus]

in the transformer1 try function nulltovalue() for 'Exterior_MXADDR' and give some default value for the null.

If the derivation column having null values..transformer wont allow.

This may come under datastage problem i guess!!

Vairamuthu

The job looks like:

Seq File1->Transformer1->Standardize->Transformer2->Seq File2

While converting the data from Tranformer2 to Seq File2, the job is giving the following error:

APT_CombinedOperatorController(1),0: Field 'Exterior_MXADDR' from input dataset '0' is NULL. Record dropped. [transform/tfmop_functions.C:147]


Other details:
In Tranformer2, i have used a derivation for Exterior_MXADDR which is :

If DSLink74.Exterior_MXADDR = "7777777777" or DSLink74.Exterior_MXADDR = "" then "SN" else NullToEmpty(DSLink74.Exterior_MXADDR)

For SeqFile2, format properties are:
NULL Field value: NULL
Quote: None
Delimiter: | ( same are the porperties for SeqFile1 too)


Hence, as far as i see, i have taken due care to handle the nulls, still because of NULLs in the field Exterior_MXADDR, the records are getting dropped. What can be the solution.

[emeri1md]

Another thing to think about is IsNull(...). We use this a lot on my project. If it is NULL, then change it to an empty string('') or whatever you want it to be.