Help in setting Null value..
Moderators: chulett, rschirm, roy
Help in setting Null value..
Hi everybody,
I am working on datastage server edition 7.5.1 on windows xp machine with unix server using DB2.
I want to set a particular input column say customer_name to Null in the Output.I have tried to use @Null,@Null.Str,etc in the transformer but all attempts have failed.I tried extensive searches in this forums as well as in the help of the datastage but in vain.The are some functions like Setnull() and Makenull(), but i am unable to use them in the tranformer for setting null.
Please help me set Null value for a particular field or column.I have tried "",'' and \\ to set null but they give an impression of having set to null which in fact is not the case.
Thanks in advance.
Cheers!
I am working on datastage server edition 7.5.1 on windows xp machine with unix server using DB2.
I want to set a particular input column say customer_name to Null in the Output.I have tried to use @Null,@Null.Str,etc in the transformer but all attempts have failed.I tried extensive searches in this forums as well as in the help of the datastage but in vain.The are some functions like Setnull() and Makenull(), but i am unable to use them in the tranformer for setting null.
Please help me set Null value for a particular field or column.I have tried "",'' and \\ to set null but they give an impression of having set to null which in fact is not the case.
Thanks in advance.
Cheers!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
@NULL is the correct mechanism. How are you loading the DB2 table? The problem might be occurring there. You might like to edit the generated SQL to provide NULL explicitly as part of the SQL syntax.
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.
hey ray,
wel i tried out the @null in the tranformer but it is rejecting all the valid rows from reaching the DB@ output stage.Also i cannot put it in the query as it is self generated and i am not writing a user defined query.
Please tell me is there no way that i can set the entire column values as null..from the transformer using some functions.
Thanks in advance.
wel i tried out the @null in the tranformer but it is rejecting all the valid rows from reaching the DB@ output stage.Also i cannot put it in the query as it is self generated and i am not writing a user defined query.
Please tell me is there no way that i can set the entire column values as null..from the transformer using some functions.
Thanks in advance.
As Ray has said, the @NULL is the correct value to use. Are you certain that the column is nullable in the database? What is your rejection error that you are getting?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Thanks
Hey everyone,
ya thanks a lot my problem has been solved.I had done a very stupid mistake wherein i had set the column that i was trying to make Null as Not Null in the DB2.
So @Null works perfectly fine.
Thanks a tonne.
Cheers!
ya thanks a lot my problem has been solved.I had done a very stupid mistake wherein i had set the column that i was trying to make Null as Not Null in the DB2.
So @Null works perfectly fine.
Thanks a tonne.
Cheers!
-
- Participant
- Posts: 26
- Joined: Tue Nov 08, 2005 5:36 am
I have a very similar problem. I am able to insert NULL values to the DB2 Table using @NULL function in server job transformer stage. When i check the table with "select * from table1",i am able to see the "Null" values(in col1). But when i execute the query "select * from table1 where col1 is null (assuming col1 have null values) i am getting zeros records. I am confused the what the @NULL Function will insert.
-
- Participant
- Posts: 26
- Joined: Tue Nov 08, 2005 5:36 am
I have a very similar problem. I am able to insert NULL values to the DB2 Table using @NULL function in server job transformer stage. When i check the table with "select * from table1",i am able to see the "Null" values(in col1). But when i execute the query "select * from table1 where col1 is null (assuming col1 have null values) i am getting zeros records. I am confused the what the @NULL Function will insert. Please help me in this.
Thanks for your support.
Thanks for your support.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
@NULL remains the correct way to generate null. Perhaps your database converts null to "" on loading. Check with your DBA. What stage type are you using to effect the load?
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: 26
- Joined: Tue Nov 08, 2005 5:36 am