HandleNull
Moderators: chulett, rschirm, roy
DataStage EE 7.5x2
Server OS: Window 2003
For Null Handling, how are you tackling DATE ?
When the Input and Output column is delcared as DATE type, is having trouble to use the available NULL Handling function on dealing with Null date column from input and mapping to an output column.
The input data row with Null date got dropped as stated by PX.
Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either
IF IsNull(InDate) Then ... Else .... ==> No effect either
Have I missed or mixed up a bit ? or there is a bug ??
Thanks in advance for any tips to get around this.
Server OS: Window 2003
For Null Handling, how are you tackling DATE ?
When the Input and Output column is delcared as DATE type, is having trouble to use the available NULL Handling function on dealing with Null date column from input and mapping to an output column.
The input data row with Null date got dropped as stated by PX.
Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either
IF IsNull(InDate) Then ... Else .... ==> No effect either
Have I missed or mixed up a bit ? or there is a bug ??
Thanks in advance for any tips to get around this.
cwong
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,
Yes, the input column was qualified with the link name.
The example was just a short-hand typing to illustrate the issue.
IF IsNull( SrcInputData.LAST_SNAP_DT ) Then SetNull() Else SrcInputData.LAST_SNAP_DT
Is this what you meant ?
This transformation works for DataSet except Sequential file.
Please advise ...
Thanks.
Yes, the input column was qualified with the link name.
The example was just a short-hand typing to illustrate the issue.
IF IsNull( SrcInputData.LAST_SNAP_DT ) Then SetNull() Else SrcInputData.LAST_SNAP_DT
Is this what you meant ?
This transformation works for DataSet except Sequential file.
Please advise ...
Thanks.
cwong
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Since there is a virtual Data Set between your Transformer stage and your Sequential File stage (you can see this in the generated OSH), if there is a bug or a design problem it's within the Sequential File stage. Can you thoroughly check the metadata in the Sequential File stage and its input link, even to the extent of opening the Edit Row dialog for the LAST_SNAP_DT column, to see if there's anything there that might cause this behaviour? Add a Peek stage between your Transformer stage and your Sequential File stage to capture what's leaving the Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard. :D
The five most experienced posters on this site are "premium posters". What that means, in effect, is that their replies are considered to have some value, and a small charge (less than $1 per week) is levied so that you can read the premium posts. This revenue helps towards the costs of running this site.
You would pay considerably more to have even one question answered on a commercial basis.
Of course, you can choose to direct all your questions to your support provider (paying maintenance entitles you to do so) but you will find a wealth of practical experience here - not just from the premium posters - that your support provider may not have. On the other hand, your support provider is probably more beneficial in getting bugs resolved.
This post contains additional information about handling NULL in the Modify stage.
The five most experienced posters on this site are "premium posters". What that means, in effect, is that their replies are considered to have some value, and a small charge (less than $1 per week) is levied so that you can read the premium posts. This revenue helps towards the costs of running this site.
You would pay considerably more to have even one question answered on a commercial basis.
Of course, you can choose to direct all your questions to your support provider (paying maintenance entitles you to do so) but you will find a wealth of practical experience here - not just from the premium posters - that your support provider may not have. On the other hand, your support provider is probably more beneficial in getting bugs resolved.
This post contains additional information about handling NULL in the Modify stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Your example code is setting the ouptut value to null if the input is null, otherwise it sets it to itself. What is the point of that?
If you are then writing this to a sequential file you need to handle the null differently. Remember there is no such thing as a null in a sequential file. You have to write something to the file, either a character that will represent your null values or just an empty string.
If you are then writing this to a sequential file you need to handle the null differently. Remember there is no such thing as a null in a sequential file. You have to write something to the file, either a character that will represent your null values or just an empty string.
Yes, you are correct about the target column needing to be a NON-null value.
My question is more about Null handling for a DATE type column.
Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either
emm ... probably should try something like this:
NullToValue( InDate, StringToDate('1900-01-01') )
Will try this out tomorrow to see ... or if you have any idea or experience in handling this ...
BTW, the
IF IsNull(InDate) Then SetNull() Else InDate
is used to fulfill the Null Handling requirement to avoid the data row with Null value got dropped. Technically, there is no effective in the end result.
My question is more about Null handling for a DATE type column.
Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either
emm ... probably should try something like this:
NullToValue( InDate, StringToDate('1900-01-01') )
Will try this out tomorrow to see ... or if you have any idea or experience in handling this ...
BTW, the
IF IsNull(InDate) Then SetNull() Else InDate
is used to fulfill the Null Handling requirement to avoid the data row with Null value got dropped. Technically, there is no effective in the end result.
cwong
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Whatever replacement date value you give must correspond exactly to the date picture currectly specified for the job, unless you override that with a formatting option (for example in the handle_null() specification in a Modify stage).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.