Fatal Error: Invalid Julian day in Filter Stage

dougcl · Posted: Fri Jul 30, 2010 3:29 pm

Hi folks, Peek stage shows this data:

Code:

RECORDED_TIME:2008-09-23 11:36:00 EFFECTIVE_TIME:2008-09-20 03:14:00 TO_TIME:2008-09-20 14:10:00

Filter stage with this where clause:

Code:

RECORDED_TIME BETWEEN EFFECTIVE_TIME AND TO_TIME

or with this where clause:

Code:

RECORDED_TIME >= EFFECTIVE_TIME AND RECORDED_TIME < TO_TIME

either way results in this error:

Code:

Fatal Error: Invalid Julian day

What am I doing wrong? Does this comparison require a date datatype instead of a timestamp datatype?

Thanks,
Doug

Sreenivasulu · Posted: Sun Aug 01, 2010 5:30 pm

You can use the isValid function to validate whether the input date is in the correct format.

Regards
Sreeni

ArndW · Posted: Mon Aug 02, 2010 2:47 am

What is the datatype of the EFFECTIVE_TIME and TO_TIME columns? Can you convert these explicitly to timestamp before the filter stage?

dougcl · Posted: Mon Aug 02, 2010 8:52 am

ArndW wrote:

What is the datatype of the EFFECTIVE_TIME and TO_TIME columns? Can you convert these explicitly to timestamp before the filter stage?

DataStage timestamp. All three are DS timestamp.

ArndW · Posted: Mon Aug 02, 2010 9:03 am

I just put together a quickie job, Row Generator with 2 timestamp colums and put them through a filter without any errors or warnings. If you add $OSH_PRINT_SCHEMAS to your job and look at the actua ...

What's this?

dougcl · Posted: Mon Aug 02, 2010 5:46 pm

ArndW wrote:

add $OSH_PRINT_SCHEMAS to your job and look at the actual schemas going into your filter stage are they actually timestamp?

Code:

RECORDED_TIME: nullable timestamp;
EFFECTIVE_TIME: timestamp;
TO_TIME: nullable timestamp;

Doing the obvious and removing "nullable" led to a null error, so I think that must be the problem. I am getting a null in there, which is obviously not a julian day. Thanks for your help.
Doug