Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Application of predicates or filters before materialising view definition


    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
    , 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)

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    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.
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    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: 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.
    Last edited by stolze; 09-02-09 at 04:01.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts