I have searched this forum on the many ways to handle null values. I saw several posts that specified that some handling can be setup on the source and target stages through the "edit column metadata properites" dialog box. However, when I try to use this method, (Note: I have tried for all of the database stages) I cannot specify anything. I have tried for char nullable, varchar nullable, decimal nullable, date nullable, etc...
I always get
Properties:
None Available
No schema properties can be specified at this point.
I must be missing something? I have tried the documentation and it is not helping me with what I am missing.
Specifying null value attributes through column properties?
Moderators: chulett, rschirm, roy
-
Ultramundane
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Hi,
It depends on what job type you refer to.
having that clear (posting on EE forum with Server job type in post is confusing!)
If you mean the nullable and pad character options of the columns/stage tabs in the stages it's similar in both EE and Server jobs, Nullable only indicates that the column can be null as far as the meta data is concerned and Padding substitutes Missing values with a givven character.
As for null handling you can use:
handle_null(<link.col>,<value>)
in modify stage or the NullTo...() conversion functions and others in a Transformer for EE or PX jobs
Or The:
If IsNull(link.col) Then <something> Else <Something>
of the Server job transformers
IHTH,
It depends on what job type you refer to.
having that clear (posting on EE forum with Server job type in post is confusing!)
If you mean the nullable and pad character options of the columns/stage tabs in the stages it's similar in both EE and Server jobs, Nullable only indicates that the column can be null as far as the meta data is concerned and Padding substitutes Missing values with a givven character.
As for null handling you can use:
handle_null(<link.col>,<value>)
in modify stage or the NullTo...() conversion functions and others in a Transformer for EE or PX jobs
Or The:
If IsNull(link.col) Then <something> Else <Something>
of the Server job transformers
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
Ultramundane
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Posting on EE forum with Server job type in post is confusing! Yeah, I made a mistake and posted twice. Shouldn't have done that.
Thanks, your input it makes sense. I still don't understand why I don't have the capability from the source input columns to specify this when the source stage is a database.
Another question, I have a Sybase Enterprise -> Sybase Enterprise job and it blows up because Datastage cannot handle the Sybase Datetime (Ascential timstamp) as being nullable. I can of course in the select statement specify a default value or I can specify a value using the methods you specified. But, this corrupts my data. I mean, a NULL means unknown and a value means known and this is what it is. So, why cannot I pull a timestamp null from my source and load directly to my target without corrupting my data? I am confused. Is this how we must handle NULLs? That is, my transforming our data into something that does not represent the source data and is thus corrupt?
Thanks, your input it makes sense. I still don't understand why I don't have the capability from the source input columns to specify this when the source stage is a database.
Another question, I have a Sybase Enterprise -> Sybase Enterprise job and it blows up because Datastage cannot handle the Sybase Datetime (Ascential timstamp) as being nullable. I can of course in the select statement specify a default value or I can specify a value using the methods you specified. But, this corrupts my data. I mean, a NULL means unknown and a value means known and this is what it is. So, why cannot I pull a timestamp null from my source and load directly to my target without corrupting my data? I am confused. Is this how we must handle NULLs? That is, my transforming our data into something that does not represent the source data and is thus corrupt?