Assignment of a NULL value to a NOT NULL

kennyapril · Post by **kennyapril** » Sun Nov 07, 2010 1:08 pm

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.

chulett · Post by **chulett** » Sun Nov 07, 2010 4:24 pm

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.

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 9:29 am

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.

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 10:59 am

Code: Select all

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

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

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 11:17 am

Datatype and nullability for the transformer and target stage is

Code: Select all

char and not NULL

Thanks

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 11:33 am

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

Code: Select all

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

As a precaution, use varchar datatype wherever possible unless char is requirement.

chulett · Post by **chulett** » Mon Nov 08, 2010 11:36 am

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.

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 11:43 am

Thanks Craig, forgot about this possibility.

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 11:56 am

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

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 12:05 pm

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.

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 12:51 pm

Yes value in $APT_PAD_CHAR variable is 0x20

Thanks,

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 12:54 pm

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

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 1:03 pm

sure

this is the column derivation used in the transformer

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

Thanks,

nitkuar · Post by **nitkuar** » Mon Nov 08, 2010 1:07 pm

it looks fine...

try using this one:

Code: Select all

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

Post error log as well.

kennyapril · Post by **kennyapril** » Mon Nov 08, 2010 1:19 pm

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.