Sequential file read problem with a timestamp

[sajidkp]

Hi,
I have a seq file having columns

d1 Name d2 d3
--------------------------------------------------------------

2009-05-11 11:39:27|as|2009-05-11 11:39:27|


with pipe as delimitter. In this case when i havnt given a value for d3 column which is a timestamp type , its giving error . when value is supplied as below it working

2009-05-11 11:39:27|as|2009-05-11 11:39:27|2009-05-11 11:39:27

In former case i have put property of the column as nullable still i am getting same error

The error is as follows

##W IIS-DSEE-TFIG-00176 14:37:05(000) <Sequential_File_0,0> Input buffer overrun at field "d3", at offset: 43
##W IIS-DSEE-TOIX-00154 14:37:05(001) <Sequential_File_0,0> Import warning at record 0.
##W IIS-DSEE-TOIX-00018 14:37:05(002) <Sequential_File_0,0> Import unsuccessful at record 0.
>##E IIS-DSEE-TFDA-00036 14:37:05(003) <APT_CombinedOperatorController,0> Data string '
>' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': an integer was expected to match tag %yyyy.
##W IIS-DSEE-TFIG-00198 14:37:05(004) <Sequential_File_0,0> Field "d" delimiter not seen, at offset: 19
##W IIS-DSEE-TFIG-00190 14:37:05(005) <Sequential_File_0,0> Input buffer overrun at field "d"
##W IIS-DSEE-TOIX-00154 14:37:05(006) <Sequential_File_0,0> Import warning at record 1.
##W IIS-DSEE-TOIX-00018 14:37:05(007) <Sequential_File_0,0> Import unsuccessful at record 1.
##I IIS-DSEE-TOIX-00163 14:37:05(008) <Sequential_File_0,0> Import complete; 0 records imported successfully, 2 rejected.
**VIEWDATA ROW LIMITER HIT**
>##E IIS-DSEE-TFOR-00089 14:37:05(009) <_ABORT_IDENT_,0> The runLocally() of the operator failed.
##I IIS-DSEE-TFOR-00163 14:37:05(010) <_ABORT_IDENT_,0> Input 0 consumed 0 records.
>##E IIS-DSEE-TFOR-00089 14:37:05(011) <APT_CombinedOperatorController,0> The runLocally() of the operator failed.



Please anyone help me

[nirdesh2]

You can read the data using varchar and then convert it into Timestamp if the column value is not null.

[sajidkp]

You can read the data using varchar and then convert it into Timestamp if the column value is not null.

But.. Some times The value in Timestamp filed will be null. What would be the correct way to read a column with type timestamp which can have nul values also????

[nirdesh2]

If Timestamp column is having null values then correct way to read the timestamp column will be using varchar datatype and then convert it into Timestamp. If value is null then use setnull() function to set the null values.

[nirdesh2]

If Timestamp column is having null values then correct way to read the timestamp column will be using varchar datatype and then convert it into Timestamp. If value is null then use setnull() function to set the null values.
If IsNotNull(d3) Then StringToTimestamp(d3) Else SetNull()

[sajidkp]

If Timestamp column is having null values then correct way to read the timestamp column will be using varchar datatype and then convert it into Timestamp. If value is null then use setnull() function to set the null values.
If IsNotNull(d3) Then StringToTimestamp(d3) Else SetNull()

Hi Nirdesh ,
I have done as u said . But still i am getting a warning as follows

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

I have set prperty to Nullable in the output file and done an ISNULL check and setnull() . still i am getting the above warning

[nirdesh2]

Set the NullFieldValue in Format tab under the default value in target sequential file. Set it to "".

[nagarjuna]

Check the extended properties of the column with which you having the problem and enter the correct entry in null field value