import error and no default value for only one column?

joesat · Post by **joesat** » Tue Sep 04, 2007 11:09 pm

The following error has already been discussed at length
Field "FIELD_NAME" has import error and no default value; data: <empty>, at offset: 132

But I am having a peculiar case.I have over 80 columns where I haven't specified any default values. But Datastage is throwing up this error only for one particular column which is in the 15th position. There are around 10 more columns having the same datatype and length, prior to this particular column, and where I have not specified any default. But no errors are shown for those fields.

Why should the error be shown for only one field and not the rest even though they have the same metadata?

ArndW · Post by **ArndW** » Tue Sep 04, 2007 11:21 pm

Is this the first column of that datatype? If so, then it is probably because the engine parses left-to-right and stop when it encounters the bad one.

joesat · Post by **joesat** » Tue Sep 04, 2007 11:29 pm

No, but this is the first column of the DECIMAL type which encounters a null value.

Once I set a default value for this field, the same error crops for another column FIELD_2 with the same decimal datatype.

I don't understand why this error should come up even after I have specified the NULL FIELD VALUE property. Why does it expect me to also give the DEFAULT property? What is the difference between the two properties?

Sorry for asking too many questions...but I guess there is no other way :D

ArndW · Post by **ArndW** » Tue Sep 04, 2007 11:34 pm

Are you sure that it is getting a null value or just an empty value?

joesat · Post by **joesat** » Tue Sep 04, 2007 11:47 pm

The file is a pipeline delimted file. Let me give you a sample

0|44.65|0.00|44.65|44.65||

As you can see the last column doesn't have any value. Does that mean it is empty or is it null? I am not sure...

ArndW · Post by **ArndW** » Tue Sep 04, 2007 11:54 pm

It quite definately is empty and not null. Sequential files don't have null columns.

joesat · Post by **joesat** » Wed Sep 05, 2007 12:08 am

Earlier when I did not give the NULL FIELD VALUE property, I got the following error:

Sequential_File_3: When checking operator: When validating export schema: At field "FIELD_NAME": Exporting nullable field without null handling properties

so the best way to get out of this situation is to specify both NULL FIELD VALUE and the DEFAULT properties, is it not?

ag_ram · Post by **ag_ram** » Wed Sep 05, 2007 12:18 am

if you have
a||c
and all are varchar fields , the second field will be imported as "Empty space".
For other datatypes , this is not the case . There will be warnings and the records will be dropped if reject file is not mentioned.

Nutshell:Varchar fields are read as "Empty spaces" unless specifically NULL Field value is specified , not true for other datatypes.

ag_ram · Post by **ag_ram** » Wed Sep 05, 2007 12:19 am

if you have
a||c
and all are varchar fields , the second field will be imported as "Empty space".
For other datatypes , this is not the case . There will be warnings and the records will be dropped if reject file is not mentioned.

Nutshell:Varchar fields are read as "Empty spaces" . if these have to be taken as NULL , NULL Field value needs to be specified , for other datatypes this is not quite the case.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 05, 2007 12:25 am

Try also setting the Default property for the appropriate data type.

joesat · Post by **joesat** » Wed Sep 05, 2007 3:41 am

so what exactly is the difference between a NULL field and an EMPTY field?

For example, my record looks like this in a sequential file:

10.00||20.00

where all the fields are decimal values.

But when I use the View Data option in Datastage, the record is displayed as
10.00 NULL 20.00

So how does one differentiate a NULL field from an EMPTY field?