Handling invalid data in data column

[highpoint]

Hi,

We are reading from the source view with the datatype as "Date" whereas the column is defined as timestamp in datastage.

The job has been working for a long time.But recently we started getting issue. The problem is this column is getting is some wierd data.
We implemented the ISValid() function.

The data is such that it passes the isvalid() but fails in the next stages.

If we run the SQL direclty against the Database. Only for that particular key we are getting the following error:

Code: Select all

select to_timestamp(start_date) from details
where id = 222

Gives the error" ORA-01847:day of month must be between 1 and last day of month"

How shall we handle this scenario.

[ray.wurlod]

What's the default Oracle date picture? Could bad data actually have been stored in the table by any means?

[highpoint]

What's the default Oracle date picture? Could bad data actually have been stored in the table by any means?

Yes, bad data is present in table. But the job is failing, we want the job to handle this by making it to a default value or anything like that.

I tried using the ISvalid() then also that particular record with the wierd data is passing through.

I used dump function on the "date" column where my job is failing.

dump(start_date) returns "Typ=12 Len=7: 44,111,2,1,1,1,1" ---> For the record with issue
For all other record dump(start_date) returns "Typ=12 Len=7: 120,111,5,1,1,1,1".

Appreciate help.

[sivanagu]

Hi,

How the wierd data is allowing to load in source table even the column defined as date.

[highpoint]

Hi,

How the wierd data is allowing to load in source table even the column defined as date.

That what we dont know. But we need to handle it in our code no matter what is the source sending.

[ray.wurlod]

Then try reading it as CAST (column AS VARCHAR2) and handling the variants within DataStage.