DSXchange

Hello,

I was trying to load some records in the table but none of the records are inserted because one of the record has null value and the column has not null property.

please suggest a function to use in the transformer which changes the null records to some value.

Read up on NullToZero() and NullToValue() then use whichever is appropriate. Or simply use IsNull() to test and then assign whatever you like. Sam Ting.

I used something like

If IsNull(DSLink44.user_name) then ' ' Else DSLink44.user_name

in the derivation of column in transformer.


but did not work

please let me know if any changes are required.

this should have been worked...please specify datatype and nullability of this column in transformer and your target stage.

Datatype and nullability for the transformer and target stage is
Thanks

I guess, datatype of this field in source stage is also CHAR and you are using default pad character 0x00. That is the reason you are not able to compare it with null.

Add $APT_PAD_CHAR environment variable in your job as job parameter and change its default value to 0x20.

Then in transformer stage, trim it before using IsNull() function As a precaution, use varchar datatype wherever possible unless char is requirement.

If we're talking Oracle here, spaces get trimmed and you still end up with a null in the target. For that, add $APT_ORACLE_PRESERVE_BLANK to your job and set it to True.

Thanks Craig, forgot about this possibility.

craig,

The target is not oracle thats a db2,so do I need to use the environment variable.

nitkuar,

used the code and used padchar also set to 0X20.
now I get a compilation error in the transformer and got the msg




##I IIS-DSEE-TOSH-00002 11:54:01(001) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 11:54:01(002) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 11:54:01(003) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 11:54:01(006) <main_program> Parameter specified but not used in flow: DSPXWorkingDir (dsproject2.Transformer_50)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S50_dsproject2_Transformer_50 transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 DSLink44;
0007: outputname 0 DSLink51;
0008:
0009: initialize {
0010: // define our row rejected variable
0011: int8 RowRejected0;
0012:
0013: // define our null set variable
0014: int8 NullSetVar0;
0015:
0016: // declare our intermediate variables for this section
0017: string InterVar0_0;
0018:
0019: // initialise constant values which require conversion
0020: InterVar0_0 = " ";
0021: }
0022:
0023: mainloop {
0024: // initialise our row rejected variable
0025: RowRejected0 = 1;
0026:
0027: // evaluate columns (no constraints) for link: DSLink51
0028: if (null(trimc_string(DSLink44.user_name))) {
0029: DSLink51.user_name = InterVar0_0;
0030: } else {
0031: DSLink51.user_name = DSLink44.user_name;
0032: }
0033: //;
0034: writerecord 0;
0035: RowRejected0 = 0;
0036: }
0037:
0038: finish {
0039: }
0040:
*** End of Internal Generated Transformer Code

Thanks

Kenny,

If target is DB2 then $APT_ORACLE_PRESERVE_BLANK is not required.

Could you please also confirm that value given in $APT_PAD_CHAR variable is 0x20 and not 0X20.

Yes value in $APT_PAD_CHAR variable is 0x20


Thanks,

could you please also post the exact column derivation of this field used in transformer stage.

sure

this is the column derivation used in the transformer

If IsNull(Trim(DSlink44.user_name)) then ' ' Else DSLink44.user_name


Thanks,

it looks fine...

try using this one: Post error log as well.

got the same compilation error earlier..

The difference I observe between the two kinds is when I use trim i get this compilation error with out trim I mean only the IsNull no errors but the data will not be loaded as the column is not null and the values are null.