Filter Stage

opdas · Post by **opdas** » Wed May 17, 2006 4:00 am

Hi
I have a requirement where I need to filter incoming data based on Date i.e there is a column Process_timestamp(data type: Time stamp), I need records where Date(Process_timestamp)=current date - 1 day

What would be the syntax in the where clause in filter stage

Any help is appreciated.

Regards
Om Prakash

ashwin141 · Post by **ashwin141** » Wed May 17, 2006 4:17 am

Hi Om

What exactly is the problem you are facing? Is it that you are unable to findout how to get the current date in the where clause or something else?

Regards
Ashwin

opdas · Post by **opdas** » Wed May 17, 2006 5:24 am

Its the syntax ,
Can I use the function used in transformer in where clause of filter stage?

ashwin141 · Post by **ashwin141** » Wed May 17, 2006 5:26 am

I am not sure

May be you can try using CurrentDate(). If it works let us know.

Ashwin

opdas · Post by **opdas** » Wed May 17, 2006 5:32 am

Hi,
I have used the below code in the "Where Clause"

Code: Select all

TimestampToDate(Process_timestamp)=DateFromJulianDay(JulianDayFromDate(CurrentDate())-1)

When run geting the below Fatel error:

Code: Select all

Filter_36: Parse error: Expected comparison, 'between', or 'like' operator, got: "("
Parse error: Expected boolean operator (and, or) got: "Process_timestamp".

ccatania · Post by **ccatania** » Wed May 17, 2006 9:11 am

You can calculate the date in a Tranform Stage Varaible, place the value in an ouput field. Use that field in your filter.

DSguru2B · Post by **DSguru2B** » Wed May 17, 2006 11:05 am

You can add it as a constraint in the transformer. This will filter all your records.

opdas · Post by **opdas** » Wed May 17, 2006 10:30 pm

Yes, Thats what I'm doing now generating two new columns in the transformer stage for current date - 1 day and converting timestamp to date in another and comparing it in filterstage.
OR
I can filter it inside the transformer it self and I dont need a filter stage.

But what I thought was Filter stage should be exhaustive enough to handle complex filters , otherwise the very purpose of having a filter stage fails !