Is there a better way to handle nulls ???
Moderators: chulett, rschirm, roy
Is there a better way to handle nulls ???
Loading data from a fixed with Sequential File to a DB2 Table. Some of the records I load consists of null columns...they are of data type date, timestamp,char,integer,decimal
for date and timestamp I use a validation for the null handling....
For the others data types if I dont validate I get warnings.....
Is it necessary for me to validate the columns with other data types for nulls...isn't there a simpler way to perform this....?
Thanks
for date and timestamp I use a validation for the null handling....
For the others data types if I dont validate I get warnings.....
Is it necessary for me to validate the columns with other data types for nulls...isn't there a simpler way to perform this....?
Thanks
Simple and better way is handle Null's coming from source column's explicitly:
1). When Tagret column is defined as Not Null irresptive of data types.
2). When Target column is defined as Numeric, Integer, Decimal, Small Int, Float..........other data types which are defined to accept only Numeric values even if the column is set accept Null values.
![Laughing :lol:](./images/smilies/icon_lol.gif)
1). When Tagret column is defined as Not Null irresptive of data types.
2). When Target column is defined as Numeric, Integer, Decimal, Small Int, Float..........other data types which are defined to accept only Numeric values even if the column is set accept Null values.
![Laughing :lol:](./images/smilies/icon_lol.gif)
Re: Is there a better way to handle nulls ???
Yeah it is necessary if your target requires that column to be not null.cosec wrote: Is it necessary for me to validate the columns with other data types for nulls...isn't there a simpler way to perform this....?
And, what's so complex about handling nulls?
![Confused :?](./images/smilies/icon_confused.gif)
gateleys[/b]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A typical scenario with Fixed width flat file will always represent NULL as spaces equal to the field length.
DataStage will never consider space as a valid representation for NULL.
I will do a Length after a Trim Validation to find out if its a NULL representation for a field or not.
But using this feature should be considered conciously and assumed as a data representation with spaces will be lost during this transformation.
Another approach is if all the available characters in that data are spaces then thats a NULL {Can be achieved by writing a Routine maybe} --
-K
DataStage will never consider space as a valid representation for NULL.
I will do a Length after a Trim Validation to find out if its a NULL representation for a field or not.
Code: Select all
If Len(Trim(InputLink.InputColumn)) = 0 Then SetNull() Else InputLink.InputColumn
Another approach is if all the available characters in that data are spaces then thats a NULL {Can be achieved by writing a Routine maybe} --
![Idea :idea:](./images/smilies/icon_idea.gif)
-K
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Well... yes, you have to determine which fields need to have null handling. Note that that is a blanket statement regardless of source or situation - one of your jobs as a developer is to determine if nulls will wreak havoc on your process and handle them properly. Always.
While we'd all love to have a 'simpler solution' handed to us for everything, that's not always possible. Besides, this isn't exactly a complicated solution that you need in this case - what sucks for you is the fact that it wasn't done originally and now you have to retrofit this into an 'enormous number' of jobs. Look on the bright side - it's a great object lesson.
Involve others. Share the pain!
And in this particular case, Ray has given you about the simplest solution of all - but I get the impression you keep overlooking it.![Confused :?](./images/smilies/icon_confused.gif)
While we'd all love to have a 'simpler solution' handed to us for everything, that's not always possible. Besides, this isn't exactly a complicated solution that you need in this case - what sucks for you is the fact that it wasn't done originally and now you have to retrofit this into an 'enormous number' of jobs. Look on the bright side - it's a great object lesson.
![Wink :wink:](./images/smilies/icon_wink.gif)
And in this particular case, Ray has given you about the simplest solution of all - but I get the impression you keep overlooking it.
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: