null export handling

lory · Post by **lory** » Mon Apr 04, 2005 8:49 am

Hi,
I have a job:

Oracle extraction - file sequential.

Some fields are nullable, and I want them null. But I have some discard, the message is the following:

Rss_Consistenza_file.Estraz,0: Field "FIELD1" is null but no null export handling is defined
Rss_Consistenza_file.Estraz,0: Export unsuccessful at record 3 (continuing) :
..........

Could anyone help me?

Thanks

Eric · Post by **Eric** » Mon Apr 04, 2005 9:01 am

look at the columns list in the sequential file and double click on the row number (left hand side), this brings you up another screen that allows you to set the -> Nullable -> Null Field Value.

This allows you to set the character string you are using to mean NULL that is written to file.

lory · Post by **lory** » Mon Apr 04, 2005 9:46 am

[quote="Eric"]look at the columns list in the sequential file and double click on the row number (left hand side), this brings you up another screen that allows you to set the -> Nullable -> Null Field Value.

This allows you to set the character string you are using to mean NULL that is written to file.[/quote]

I'd like to know if it is possible to leave the NULL value in the file without without being substituted by another string

ray.wurlod · Post by **ray.wurlod** » Mon Apr 04, 2005 2:43 pm

Essentially the answer is NO.

NULL is a meaningless concept in a text file. If you want the internal database representation of null, you will find that this varies between databases, and would mean that your text file is probably a binary file (there's no guarantee that the internal representation of null doesn't contain 0x0D either, which would really foul you up).

If you want the null fields in your text file to contain zero-length strings (""), do nothing. This is the default for the Sequential File stage.

lory · Post by **lory** » Tue Apr 05, 2005 3:15 am

The problem is that the record with null values are doscarded and not written in my file. Why?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 05, 2005 6:45 pm

Because you have not specified null handling.

Eric · Post by **Eric** » Wed Apr 06, 2005 4:48 am

Just to note:
When you use a 'special character sting' to represent a NULL in a text file.

When you read that text file back into DataStage you can tell DataStage that the 'special character string' means a null, so when you load data back into a database you again have a NULL value (rather that your special string).

gh_amitava · Post by **gh_amitava** » Thu Apr 07, 2005 2:48 pm

lory,

Use NVL function of Oracle in the select clause. It is best to handle null there.

Regards
~Amitava

richdhan · Post by **richdhan** » Fri Apr 08, 2005 12:11 am

Hi Amitava,

Using the NVL function in oracle will not resolve the warning. The record will not be discarded but the warning will still persist. The best way to handle this situation is using the Null handling feauture available in Field properties of sequential file stage as described by Eric and Ray.

Rich

PilotBaha · Post by **PilotBaha** » Wed Oct 26, 2005 3:24 pm

Ok, as a newbee I have a question about this null handling. I was not doing anything on null handling and the job started to give a lot of null errors. So, after doing a search on the board here, I decided to try null field length = 0 approach.

This time I am getting

Code: Select all

Error when checking operator: At field "PRUSERNAME": "null_length" may only be used in conjunction with prefix length or link length

as an error.

Dealing with these nulls is becoming pretty annoying at this point..