how to set not null column to null
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 89
- Joined: Sat Jul 24, 2010 11:33 pm
how to set not null column to null
Hi,
Iam trying to set not null column to be nullable. I dnt want to default it to the some null value. I want it to be blank. Can you please guide me how to set this in the transformer .....
Iam trying to set not null column to be nullable. I dnt want to default it to the some null value. I want it to be blank. Can you please guide me how to set this in the transformer .....
-
- Participant
- Posts: 89
- Joined: Sat Jul 24, 2010 11:33 pm
This contradicts your original post, at least there you wanted to set it to a space which could be legal. There is no way to allow a null in a column that does not allow them.developeretl wrote:It is not null column and i want it to be null when there is no data
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This won't work anywhere. If it's not null you simply can not set it to be truly null. That would break a rule, and the rule is enforced.developeretl wrote:It is not null column and i want it to be null when there is no data
Choose an "in-band null" - a value (perhaps blank) that is valid for the data type of the column and that does not occur in the data.
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.
If you want to set a Not Null field to a blank space then you can simply give " " in the transformer stage.It's a blank space.
Or if you want to set a blank space when the input column's data is Null/no data use the below derivation in the transformer stage :
If IsNull(inputcolumn) Then " " Else inputcolumn.
Or if you want to set a blank space when the input column's data is Null/no data use the below derivation in the transformer stage :
If IsNull(inputcolumn) Then " " Else inputcolumn.
-
- Participant
- Posts: 89
- Joined: Sat Jul 24, 2010 11:33 pm
Thank you for your reply.natashab wrote:If you want to set a Not Null field to a blank space then you can simply give " " in the transformer stage.It's a blank space.
Or if you want to set a blank space when the input column's data is Null/no data use the below derivation in the transformer stage :
If IsNull(inputcolumn) Then " " Else inputcolumn.
I got one more issue, Iam doing keep date field as null in the transformer stage and target is the sequencial file stage. Iam getting error " nullable field but no null handling properties", but this is working for the Dataset. The column i want to be null in target is nullable but iam not able to populate the field with null in the sequential stage but iam able to do with dataset stage ? can you please guide me
-
- Participant
- Posts: 89
- Joined: Sat Jul 24, 2010 11:33 pm
I Kris i did exactly what others had suggested me . I kept " " as NULL file value in the sequential file stage. But i got this errors ( for 5 columns with different lenths)kris007 wrote:You cannot write NULL values into Sequential File. You need to set NullFieldValue = '' in the Sequential file stage format tab.
When checking operator: When validating export schema: At field "col1": "null_field" length (1) must match field's fixed width (17)
When validating export schema: At field "col2": "null_field" length (1) must match field's fixed width (7)
can you suggest me ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 89
- Joined: Sat Jul 24, 2010 11:33 pm
Hi Kris,kris007 wrote:You will need to define null field values for each column separately.
For one column I have specified null field value but i have nearly 6 columns how do i specify them. Do i need to keep 6 null field values in the sequential stage, if so do they correspond exactly to those columns only or it might be to other columns also . How can I make sure that col1 null field value corresponds to col1 only. Similar way to col2 to col6 can you please tell me.