Hi all;
i am loading data from falt file (tab seperated ) using sequential file stage to database table using odbc stage.
in loading data from source to target, i need to do null checking on some fileds , if null those records are loaded in to reject file/table.i am using constraint to do this.
i have written my constraint like this;
isnull (filed1) and isnull( filed2) and isnull(field3) and isnull(field45) and isnull(field46) and isnull(field 47)
is the above expression right or wrong.
checking for null values
Moderators: chulett, rschirm, roy
--------------------------------------------------------------------------------
hi T42;
[Are you using
APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL? Or are you using a specific field information to define NULL values from input sequential file?
How do you identify whether the input data on a file is a null? ]
I am not using any one of above. i don't know how to identify whether the input data on a file is a nul, but i am not using a specific field information to define nul values from input sequetial file.
can u please explain how to define APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
My problem is to reject records which contain null in any one of the field
thanks
hi T42;
[Are you using
APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL? Or are you using a specific field information to define NULL values from input sequential file?
How do you identify whether the input data on a file is a null? ]
I am not using any one of above. i don't know how to identify whether the input data on a file is a nul, but i am not using a specific field information to define nul values from input sequetial file.
can u please explain how to define APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
My problem is to reject records which contain null in any one of the field
thanks
I guess you can recognise null values in such a file, when you find a zero length string between two TABs.
In this case, I suggest to insert a transformer stage between your file and odbc stage, put your condition (say: IsNull(Field01) Or IsNull(Field02) and so on) in one of the output links, and mark this link as a reject link. It will make the flow easier to understand, and the data easier to handle.
Stefano
In this case, I suggest to insert a transformer stage between your file and odbc stage, put your condition (say: IsNull(Field01) Or IsNull(Field02) and so on) in one of the output links, and mark this link as a reject link. It will make the flow easier to understand, and the data easier to handle.
Stefano
The only way you can do this is if you trigger "APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL". It will not work otherwise.ElBandido wrote:I guess you can recognise null values in such a file, when you find a zero length string between two TABs.
Bunny mentioned that he/she does not know about this, so I am awaiting his response on what his input file is using to indicate "Null". Empty space is one such instance. There are many other solutions, and the correct answer depends on what this is.
Seriously, Bunny, you need some heavy duty DataStage training provided by Ascential. They cover subjects like this, and would go a long way in helping you understand how this works.