Team,

We are using rank over() DB2 funtion in defintion of one the view of my application. We need to apply certain filters like time period etc before rank over() function is evaluated.

However, as rank over() is within view, view definition materialises first and then apply time filter on top of it, resulting in incorrect results.

Would you have any alternative or suggestion to implement my requirement.

View Defintion is :

create view vdw02.v_clist as
(select
a.ord_no
, a.base_id || a.basic_no as c
, rank()over (partition by a.base_id, a.basic_no order by a.ORD_NO,a.BASE_ID,a.BASIC_NO,a.MKT_CHAN_CD asc) as rnk
, rank()over (partition by a.base_id, a.basic_no order by (rank()over (partition by a.base_id, a.basic_no order by a.ORD_NO,a.BASE_ID,a.BASIC_NO,a.MKT_CHAN_CD asc)) desc) as rnk2
from dw02.clist a)

I'm not sure what you're asking, but views don't "materialize" in DB2; you may want to check the execution plan to see what the query looks like after the rewrite.

Right, a view doesn't "materialize". The thing is that the view dynamically computes a table. If you query that view and apply additional predicates, the predicates are applied on the result of the view and not on the input for the view, i.e. the tables being joined. That's just a basic design principle for views.

So you have some choices:

1. define a side-table that is joined-in by the view to filter out rows in the view's WHERE clause; you will have to maintain this table before querying and it will bring along locking issues
2. remove the RANK() function from the view definition and do it in the SQL statement that queries the view instead.
3. encapsulate the whole logic into a stored procedure and build the query dynamically and return the query's result as result set of the stored procedure

Maybe there are more options, but those are the ones that come first to my mind.

Btw, here you find the documentation that explains how a subselect is processed: http://publib.boulder.ibm.com/infoce.../r0000875.html Now you just piece this together with the fact thatt the view occurs in the FROM clause and it becomes that you can't inject additional predicates into view's WHERE clause when querying the view - only on top of the view's result table.