Assignment of a NULL value to a NOT NULL
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Assignment of a NULL value to a NOT NULL
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.
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.
Regards,
Kenny
Kenny
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Code: Select all
If IsNull(DSLink44.user_name) then ' ' Else DSLink44.user_name
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Datatype and nullability for the transformer and target stage is
Thanks
Code: Select all
char and not NULL
Thanks
Regards,
Kenny
Kenny
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.
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
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 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
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
Regards,
Kenny
Kenny
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
it looks fine...
try using this one:
Post error log as well.
try using this one:
Code: Select all
If IsNull(TrimLeadingTrailing(DSlink44.user_name)) then ' ' Else DSLink44.user_name
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am