Null Handling

[aruncser]

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

[jneasy]

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.

[aruncser]

Yeah...it's checked but still I'm not able to print BLANK.

[ray.wurlod]

Set the Null Field Value property to BLANK for that column.

[aruncser]

I tried doing this but this is not working.I typed BLANk in null field value but its not printing BLANK in output file.

[ray.wurlod]

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).

[ramsubbiah]

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

[zulfi123786]

I tried doing this but this is not working.I typed BLANk in null field value but its not printing BLANK in output file.

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.

if you want "" in place of NULL you have to set

Code: Select all

NULL Field Value = ""

and then view the data from a text exitor like textpad

[ramsubbiah]

Hi Zulfi,

Not exactly . suppose my source field has value empty "" then if i need to populate actual NULL value these column then i need to mention Null Field value = "" . so during run time actual NULL value will be assigned.

Thanks,
Ram

[zulfi123786]

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.

[ramsubbiah]

Hi,

Thanks. Even that is what my understanding also.

Thanks,
Ram