Page 1 of 1
import error and no default value for only one column?
Posted: Tue Sep 04, 2007 11:09 pm
by joesat
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?
Posted: Tue Sep 04, 2007 11:21 pm
by ArndW
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.
Posted: Tue Sep 04, 2007 11:29 pm
by joesat
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
Posted: Tue Sep 04, 2007 11:34 pm
by ArndW
Are you sure that it is getting a null value or just an empty value?
Posted: Tue Sep 04, 2007 11:47 pm
by joesat
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...
Posted: Tue Sep 04, 2007 11:54 pm
by ArndW
It quite definately is empty and not null. Sequential files don't have null columns.
Posted: Wed Sep 05, 2007 12:08 am
by joesat
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?
Posted: Wed Sep 05, 2007 12:18 am
by ag_ram
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.
Posted: Wed Sep 05, 2007 12:19 am
by ag_ram
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.
Posted: Wed Sep 05, 2007 12:25 am
by ray.wurlod
Try also setting the Default property for the appropriate data type.
Posted: Wed Sep 05, 2007 3:41 am
by joesat
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?