Null handling

[friend.kak@gmail.com]

Hi,

I am trying to load a column (varchar). version 8.01

my logic is like this.
If this is coming as blank from inputs, then i must put NULL into that target column. How to handle this, when i am tryingto do this in xfm , it is throwing an compilation error, could not compile.

Logic is:
-----------------------------------------------------
if (link1.col1) = ' ' then setnull() else link1.col1
-----------------------------------------------------

Is there something wrong in this? @NULL system variable is not in the list, do we have to do some manual work to add @N?ULL in systame variables list.

Thanks.
friend.kak@gmail.com

[friend.kak@gmail.com]

yes, its been done......

if Not(IsNull(DSLink3.a_char) or DSLink3.a_char = "") then '' else DSLink3.a_char
DSLink4.col2

[friend.kak@gmail.com]

Was there sometghing wrong in this?


if (len(TrimLeadingTrailing(Trn_Track_Out_Dttm.MASK_SET_ID))=0) then SetNull() else (Trn_Track_Out_Dttm.MASK_SET_ID)

showing compilation errors.l Thanks.

[friend.kak@gmail.com]

Was there sometghing wrong in this?


if (len(TrimLeadingTrailing(Trn_Track_Out_Dttm.MASK_SET_ID))=0) then SetNull() else (Trn_Track_Out_Dttm.MASK_SET_ID)

showing compilation errors.l Thanks.

[ray.wurlod]

Can't help you unless you post the actual compilation error.

[friend.kak@gmail.com]

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 04:19:46(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.0.1.4760
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved



##I IIS-DSEE-TOSH-00002 04:19:46(001) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 04:19:46(002) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 04:19:46(003) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 04:19:46(004) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##W IIS-DSEE-TFTM-00012 04:19:46(005) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##E IIS-DSEE-TFEV-00026 04:19:46(006) <transform> Error when checking composite operator: Setting null to this non-nullable field: StageVar0_StageVar2.
##E IIS-DSEE-TFSR-00019 04:19:46(007) <main_program> Could not check all operators because of previous error(s)
##E IIS-DSEE-TCOS-00029 04:19:46(008) <main_program> Creation of a step finished with status = FAILED. (CopyOfReject_Activities_Inst2.Trn_Track_Out_Dttm)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S241_CopyOfReject_Activities_Inst2_Trn_Track_Out_Dttm transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 Trn_Track_Out_Dttm;
0007: outputname 0 Ln_Agg_Max_Counter;
0008: outputname 1 Ln_Jns_Counter;
0009: outputname 2 Dset_TrackOutTime;
0010:
0011: initialize {
0012: // define our row rejected variable
0013: int8 RowRejected0;
0014:
0015: // define our null set variable
0016: int8 NullSetVar0;
0017:
0018: // declare our intermediate variables for this section
0019: string InterVar0_0;
0020:
0021: // initialise constant values which require conversion
0022: InterVar0_0 = "";
0023: // Stage variable declaration and initialisation
0024: string StageVar0_StageVar;
0025: StageVar0_StageVar = "";
0026: string StageVar0_StageVar1;
0027: StageVar0_StageVar1 = "";
0028: string StageVar0_StageVar2;
0029: StageVar0_StageVar2 = "";
0030: }
0031:
0032: mainloop {
0033: // initialise our row rejected variable
0034: RowRejected0 = 1;
0035:
0036: // evaluate the stage variables first
0037: StageVar0_StageVar = string_from_date(date_from_timestamp(Trn_Track_Out_Dttm.REJ_DTTM));
0038: if (!((null(Trn_Track_Out_Dttm.MASK_SET_ID) || (Trn_Track_Out_Dttm.MASK_SET_ID == InterVar0_0)))) {
0039: StageVar0_StageVar1 = InterVar0_0;
0040: } else {
0041: StageVar0_StageVar1 = Trn_Track_Out_Dttm.MASK_SET_ID;
0042: }
0043: //;
0044: NullSetVar0 = 0;
0045: if ((string_length(trim_leading_trailing(Trn_Track_Out_Dttm.MASK_SET_ID)) == 0)) {
0046: NullSetVar0 = 1;
0047: } else {
0048: StageVar0_StageVar2 = Trn_Track_Out_Dttm.MASK_SET_ID;
0049: }
0050: StageVar0_StageVar2 = (NullSetVar0 == 1) ? set_null() : StageVar0_StageVar2;
0051:
0052: // evaluate columns (no constraints) for link: Ln_Agg_Max_Counter
0053: Ln_Agg_Max_Counter.REJ_CMNT = set_null();
0054: Ln_Agg_Max_Counter.REJ_DT = date_from_string(StageVar0_StageVar);
0055: writerecord 0;
0056: RowRejected0 = 0;
0057: // evaluate columns (no constraints) for link: Ln_Jns_Counter
0058: Ln_Jns_Counter.REJ_DT = date_from_string(StageVar0_StageVar);
0059: NullSetVar0 = 0;
0060: if ((string_length(trim_leading_trailing(Trn_Track_Out_Dttm.MASK_SET_ID)) == 0)) {
0061: NullSetVar0 = 1;
0062: } else {
0063: Ln_Jns_Counter.MASK_SET_ID = Trn_Track_Out_Dttm.MASK_SET_ID;
0064: }
0065: Ln_Jns_Counter.MASK_SET_ID = (NullSetVar0 == 1) ? set_null() : Ln_Jns_Counter.MASK_SET_ID;
0066: writerecord 1;
0067: RowRejected0 = 0;
0068: // evaluate constraint and columns for link: Dset_TrackOutTime
0069: if (null(Trn_Track_Out_Dttm.TRACK_OUT_DTTM))
0070: {
0071: Dset_TrackOutTime.TIMEREV = Trn_Track_Out_Dttm.timerev;
0072: Dset_TrackOutTime.FLAG = 3;
0073: Dset_TrackOutTime.EVENDMAINQTY = set_null();
0074: Dset_TrackOutTime.EVENDSUBNQTY = set_null();
0075: Dset_TrackOutTime.COUNTER = Trn_Track_Out_Dttm.counter;
0076: writerecord 2;
0077: RowRejected0 = 0;
0078: }
0079: }
0080:
0081: finish {
0082: }
0083:
*** End of Internal Generated Transformer Code

[ray.wurlod]

Have you searched for

The number of reject datasets "0" is less than the number of input datasets "1".

[Sainath.Srinivasan]

Give the derivation on target column rather than stage variable

[richdhan]

Hi,

You have to check for both the conditions whether the incoming value is a NULL or if it is an empty string and then set null.

You get these kind of issues when there is something logically wrong in the stage variables.

The best solution would be as Sainath suggested to put the derivation in the output column rather in the stage variable.

If you still like to use the stage variable use the following syntax and let us know

If IsNotNull(Trn_Track_Out_Dttm.MASK_SET_ID) AND Len(Trim(Trn_Track_Out_Dttm.MASK_SET_ID))>0 Then Trn_Track_Out_Dttm.MASK_SET_ID Else SetNull()

HTH
--Rich

[throbinson]

Stage Variables in EE are NON-NULLABLE.

##E IIS-DSEE-TFEV-00026 04:19:46(006) <transform> Error when checking composite operator: Setting null to this non-nullable field: StageVar0_StageVar2.

They cannot contain a value that might be evaluated to NULL. For some reason the Compiler is smart enough to prevent you from using SetNull() but the Editor is not smart enough to remove that function from being chosen.

[tjr]

In general you should never have nullable fields in expressions other then NullTo...(X) or IsNotNull(X) or IsNull(X), when they indeed can evaluate to NULL. It is much safer to write this expression:

If IsNotNull(Trn_Track_Out_Dttm.MASK_SET_ID) AND Len(Trim(Trn_Track_Out_Dttm.MASK_SET_ID))>0 Then Trn_Track_Out_Dttm.MASK_SET_ID Else SetNull()

As:

If IsNull(Trn_Track_Out_Dttm.MASK_SET_ID) then SetNull() else if Len(Trim(Trn_Track_Out_Dttm.MASK_SET_ID))>0 then Trn_Track_Out_Dttm.MASK_SET_ID else SetNull()

The first one only works if the C-Compiler prevents from evaluating the second operand of the AND if the first one is FALSE. This may not always be the case. It also depends on the actual generated C-Code.

Also Stage Variables and Not Nullable Output Columns cannot be set_null(). If you try to do so, compilation will fail.

[smishra.ds]

Please provide your inputs on the following:-

Source Column:- Varchar (Null) [Converted to varchar while reading from Database]
Target Column:- Date (Null)

Source Column can contain value less than '1753-01-01', which we have to set as '1753-01-01'.

Error getting:- Target Column is getting ***** in case Source column is NULL.

Derivation:-

if IsNull(input) Then SetNull() Else if (input)<='1753-01-01 00:00:00:0000' Then StringToDate('1753-01-01',"%yyyy-%mm-%dd") Else Timestamptodate(inputcol)