Null Handling
Moderators: chulett, rschirm, roy
Null Handling
Hi,
I'm new to datastage.I'm creating my first parallel job.
In this job,I'm reading a flat file in which there is a column having null value.Now while writing this input into an csv output file I want to have BLANK mentioned in place of that null value.
In null field value I tried writing BLANK with single/double quotes but its not working.
Any help on this !!!!!
Regards,
Arun
I'm new to datastage.I'm creating my first parallel job.
In this job,I'm reading a flat file in which there is a column having null value.Now while writing this input into an csv output file I want to have BLANK mentioned in place of that null value.
In null field value I tried writing BLANK with single/double quotes but its not working.
Any help on this !!!!!
Regards,
Arun
Regards,
Arun Yadav
Arun Yadav
Re: Null Handling
Have you checked to make sure that the field in question is flagged "Yes" as a nullable field in the Columns tab?
Cheers,
Joseph Neasy.
Cheers,
Joseph Neasy.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Then your field does not contain null. Maybe it contains "". This is not the same as null (null is "unknown value", whereas you know that "" is a zero-length string).
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: 40
- Joined: Tue Nov 11, 2008 5:49 am
Re: Null Handling
Hi,
When you say column is having null value, what is the exact value? is that empty "" or string NULL ? as far as my concern flat file will not hold the NULL value.
Null filed value property is used to read the records and assign NULL value for that column or all columns if the specified value in the Null file value property matches the actual value of the particular source column.
Hope this clarifies.
Thanks,
Ram
When you say column is having null value, what is the exact value? is that empty "" or string NULL ? as far as my concern flat file will not hold the NULL value.
Null filed value property is used to read the records and assign NULL value for that column or all columns if the specified value in the Null file value property matches the actual value of the particular source column.
Hope this clarifies.
Thanks,
Ram
Knowledge is Fair,execution is matter!
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
in the NULL field value property when you have typed 'BLANK' and ran you job, if the column of a record had NULL then your CSV file will show "BLANK" (characters "BLANK" not "" ) for that column of the record. you need to view the file using texpad as you are on windows.aruncser wrote:I tried doing this but this is not working.I typed BLANk in null field value but its not printing BLANK in output file.
if you want "" in place of NULL you have to set
Code: Select all
NULL Field Value = ""
- Zulfi
-
- Participant
- Posts: 40
- Joined: Tue Nov 11, 2008 5:49 am
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
what you need to understand is "there is no NULL in a CSV (sequential file)"
A sequential file is just a collection of text characters and since a traditional NULL cant be stored in a sequential file, the sequential file state offers us "NULL field value" option where you specity what the stage has to write when it finds NULL .
If you plan to use the sequential file in another job then you need to specify the same NULL field value at the time of writing and at the time of reading in the other job
Example:
if you have a job JOB A
Oracle stage ----->Seq file stage
and say your table data is
ID NAME
10 NULL
and i chose to use '$#$#' to hold NULL (NULL Field value='$#$#') in the sequential file stage
then your file would look like
ID NAME
10 $#$#
In the following job JOB B i am reading the seq file
Seq file stage ----->Sort stage ---->Dataset
i specity the same NULL Field value='$#$#' in the seq file stage of JOB B and JOB B will replace $#$# in the file to NULL and load it in the dataset.
A sequential file is just a collection of text characters and since a traditional NULL cant be stored in a sequential file, the sequential file state offers us "NULL field value" option where you specity what the stage has to write when it finds NULL .
If you plan to use the sequential file in another job then you need to specify the same NULL field value at the time of writing and at the time of reading in the other job
Example:
if you have a job JOB A
Oracle stage ----->Seq file stage
and say your table data is
ID NAME
10 NULL
and i chose to use '$#$#' to hold NULL (NULL Field value='$#$#') in the sequential file stage
then your file would look like
ID NAME
10 $#$#
In the following job JOB B i am reading the seq file
Seq file stage ----->Sort stage ---->Dataset
i specity the same NULL Field value='$#$#' in the seq file stage of JOB B and JOB B will replace $#$# in the file to NULL and load it in the dataset.
- Zulfi
-
- Participant
- Posts: 40
- Joined: Tue Nov 11, 2008 5:49 am