NULLS

kab123 · Post by **kab123** » Wed Apr 13, 2005 1:55 pm

Hi,

In my source csv file, I am getting some nulls without quotes ...DataStage is writing it to a seq file as a "?" instead nulls..Why is this happening..? I am using a after-job subroutine shell script to scrub "?' to NULLS..but I would like to know the reason and try to resolve it in the beginning...
any clues..?

Thanks

kab123 · Post by **kab123** » Wed Apr 13, 2005 3:00 pm

I dont see any warnings or errors in my log too...
I dont even realize what kind of problems it may lead like lookup failures etc...any ideas, Pls let me know...

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 13, 2005 3:22 pm

Do you have any NULL pad char?

kab123 · Post by **kab123** » Wed Apr 13, 2005 4:50 pm

Do you mean Default NULL padding...I leave it blank...
Here is how it is happening...
File1 is read and written to File2 -- File 1 has some nulls..written as nulls in File2.. so far fine
Again, in next job, File 2 is read and written to File 3--- here the Nulls in File 2 are wriiten as "?" in File 3...
in the same way, in some other scenarios:
nulls without quotes in csv files are read as nulls but written as "?" in o/p files..looks weired..
any idea from this...pls?

kab123 · Post by **kab123** » Wed Apr 13, 2005 4:55 pm

Sai,

And to my surprise...not all nulls in all fields do this...only few fields..
Do I need to configure NLS or something....?

Thankx

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 14, 2005 3:32 am

Have you given the quotes char? It may mis-interpret it to into '?'

MaheshKumar Sugunaraj · Thu Apr 14, 2005 10:42 am

Could you please check by passing srNVL(columnname,"") in which ever column you have Null values.

Hope this helps.

Thanks & Regards
Mahesh

kab123 · Post by **kab123** » Fri Apr 15, 2005 9:26 am

Mahesh,

What does this function do..?
Does this enclose the column in quotes ?...

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Apr 15, 2005 10:06 am

I think it is to replace NULL with quotes.

In short, something like
NULL_To_Default(YourField, Default_For_NULL)

and code being

If IsNULL(YourField) Then Default_For_NULL Else YourField

Thus the value "" will be substituted for Default_For_NULL

MaheshKumar Sugunaraj · Fri Apr 15, 2005 10:21 am

As you problem is that there a some Null values which come out as ? , in order to avoid this you could also use a srNVL(COLUMN_NAME," ") which actually puts in an empty value.

Hope this helps.

As Sainath pointed out this will put in a Empty Value.

Regards
Mahesh