Writing Nulls to Sequential File

[ghutchin]

We are a fairly mature DataStage shop but have only recently started using Enterprise Edition. All of our tables are loaded by the DBA's via database load utilities. We provide them with files that are tilde delimited with quotes around any character data. All null values are written as an empty string without quotes.

For example

123~"ABC"~~"CDE" where the third column is to be loaded as null.

I am having a difficult time getting the same result in a PX job. All null values that I'm passing to the output is still enclosed in quotes.

123~"ABC"~""~"CDE"

When this is loaded into the database, the third column is not loaded as null but as an empty string.

Does anyone have a potential solution for me?

Thank you,

Gord Hutchinson

[keshav0307]

while reading the file have you defined the null filed value as "" (empty).
if still a problem then in a transformer stage check for empty value and replace with null.

[ghutchin]

We've defined the output columns as nullable. However, whenever a null value is passed to the stage, it still comes through at ~""~ instead of ~~.

[sud]

Well, that is the way in which datastage behaves when it writes to sequential files, it compulsorily puts the quotes if you chose so. What you can do is run a script after the file is created to get rid of all "" in the file.

[ghutchin]

Unfortunately that will also get rid of all legitimate occurrances of "" in the file as well, not just the one's where they are truly null.

[sud]

Ummm, okay, so what you can do is, for null value in the format settings in sequential file stage specify something like - null and then get rid of the "null".

[ray.wurlod]

Are you certain that you have set Null Field Value property properly for this particular field?

[laknar]

Check for length in transformer stage if it is 0 then use set null function.