Purpose of setting Nullable property as 'No'??

joesat · Post by **joesat** » Tue Dec 18, 2007 1:27 am

I am quite confused as to what is the real purpose of setting the Nullable property as 'No'.

In a job, I had to read a field with a BigInt datatype from a sequential file. It was a key field, so I set the Nullable to 'No'. While testing it (reading the sequential file input), I gave a Null value for the field, and as expected PX rejected that record as it had a null value in a non-nullable field.

Now I am developing another similar job, except that the field has Date datatype. I set Nullable to 'No' like earlier and gave a null value as input for testing it. Now PX reads the record properly and does not reject the record!!! I tried with Varchar and again the record is not rejected.

The sequential file input is read properly even when there are null values in the input, and when the fields are specified as Nullable 'No' with datatype like Varchar and Data.

If it is going to allow null values in the input, what exactly is the purpose of setting the Nullable property?

Thanks in advance!

joesat · Post by **joesat** » Tue Dec 18, 2007 4:27 am

ArndW · Post by **ArndW** » Tue Dec 18, 2007 4:33 am

What has happened is that you are reading the null value in the Date field from a sequential file; but you have also set the default value for the date field, so upon reading the default value is what you are getting.

joesat · Post by **joesat** » Tue Dec 18, 2007 5:15 am

actually, I havent set the default value...

ArndW · Post by **ArndW** » Tue Dec 18, 2007 5:35 am

And if you write the date to a dataset what values are written (null or something else)?

joesat · Post by **joesat** » Tue Dec 18, 2007 5:48 am

i haven't tried writing it to a dataset, but when I wrote it out to a sequential file, the null values are written to the output..

ArndW · Post by **ArndW** » Tue Dec 18, 2007 5:50 am

Actually, you should get errors when writing nulls to a sequential file without handlers; so this means that you probably don't have a real SQL NULL but a UNIX null (0x00)

joesat · Post by **joesat** » Tue Dec 18, 2007 6:34 am

Yes, the sequential file is actually read from unix environment... so what should I do to handle this kind of null?

THanks..

ArndW · Post by **ArndW** » Tue Dec 18, 2007 7:41 am

I use both default values (for constrained columns like dates, times & numerics) and a transform stage with derivations for strings.

Minhajuddin · Post by **Minhajuddin** » Tue Dec 18, 2007 7:52 am

Go to the extended properties of the field you want to set as Not nullable, and set the Null Value to the value which you want to treat as a NULL.

For a varchar you would set the

Code: Select all

NullValue = ''

This property tells datastage to treat the value which you set as a NULL and then your job should work fine.

Minhajuddin · Post by **Minhajuddin** » Tue Dec 18, 2007 7:54 am

Go to the extended properties of the field you want to set as Not nullable, and set the Null Value to the value which you want to treat as a NULL.

For a varchar you would set the

Code: Select all

NullValue = ''

This property tells datastage to treat the value which you set as a NULL and then your job should work fine.

ArndW · Post by **ArndW** » Tue Dec 18, 2007 7:59 am

Minhajuddin - what you recommend means that an empty string is equated to SQL Null, which I believe is not the poster's intent. Also, strings containing 0x00 (the NUL value) would not correctly be converted to SQL NULL (0x280).

joesat · Post by **joesat** » Tue Dec 18, 2007 8:15 am

Sorry if I have been confusing, let me clarify...

I want PX to tell me when a null value is coming in the input field 'Field1'. But Field1 is a key column and hence cannot be nullable. In the event of a null coming in, the record should be rejected...

I thought this was the purpose of setting Nullable 'No'...

Andrw, I couldn't understand what exactly you meant when u said that you used default in both places,

Minjuddin, do you mean the Null Field Value property?

joesat · Post by **joesat** » Tue Dec 18, 2007 8:17 am

sorry for getting the names wrong

ArndW · Post by **ArndW** » Tue Dec 18, 2007 8:25 am

Ok, let us take things step by step. Normally this type of problem starts with mistaking an ASCII NUL and SQL NULL values for the same thing. Normally, a sequential file doesn't contain SQL NULLs. So as the first step let us see what kind of values we are dealing with. Use your favorite editor to look at your file and determine if you are seeing CHAR(000) {likely} or CHAR(128) {which is SQL Null, and unlikely}. Also, tell us if just one of these characters occurs or if the whole string or number is filled with repetitions of the character.