How to push down where criteria to the source from the view model query when the column involves some transformation

[skethire]

I have a string column on the source model, which I`m parsing as timestamp on the view model transformation. The problem is that when filtering through that column on the view model, the filtering is not passed to the source model. Teiid just gets all the rows, and performs the filter on the entire result set. As an optimization, is there any way to get it to pass filtering to source model in this scenario?   Of course this would involve converting the time stamp in the incoming query to string before sending it to the source model.

_____________________

This is my transformation query:

SELECT v.volume_id, v.volume_date, v.volume_date_str, parsetimestamp(v.volume_date_str, 'yyyyMMdd') AS converted_date FROM source_s.volume AS v

Thanks

srini

[rareddy]

Basically what you are looking for is to push down parsetimestamp function to source. What kinds of source you are working with. If the source system supports it and Teiid translator for that source is configured as such then it can do that!

[skethire]

Ramesh,

Thanks for your response.

My source is an oracle database.

No, I don't think this is a pushdown function issue.

When I query my view table as below,

select volume_id from viewmodel.volume where converted_date='2014-01-0100:00:00'

the generated source model query is:

SELECT g_0.volume_date_str, g_0.volume_id FROM source_s.volume AS g_0

This means, teiid is querying the string value from the source and then performing the filter on the resultset.  The obvious reason is 'converted_date' in my viewmodel.volume is a computed value.   So the above plan makes sense.

However, I want to explore if there is any way I can push that down to the source model query to allow the database to do the filtering.

Thanks

srini