Skip to Content
avatar image
Former Member

Calculation view with rank and filter unexpected result

Hi

I have a following problem.

I want to get the active orders and their mutations, stand 01.01.2017.

Order, Mutation, Date, Status

1, a, 01.08.2017, active

1, b, 01.07.2017, active

1, c, 01.06.2017, active

1, d, 01.05.2017, active

1, e, 01.04.2017, active

1, f, 01.03.2017, active

1, g, 01.02.2017, not active

1, h, 01.12.2016, not active

1, i, 01.06.2016, not active

1, j, 01.01.2016, not active

I created a calculation view with the following steps:

1. Filter: Date >= 01.01.2017

Order, Mutation, Date, Status

1, a, 01.08.2017, active

1, b, 01.07.2017, active

1, c, 01.06.2017, active

1, d, 01.05.2017, active

1, e, 01.04.2017, active

1, f, 01.03.2017, active

1, g, 01.02.2017, not active

2. Rank: Order by Date, Partition by Order Ascending, Bottom, 1

Order, Mutation, Date, Status

1, g, 01.02.2017, not active


3. Filter: Status = active

Order, Mutation, Date, Status

1, f, 01.03.2017, active

I would expect the last filter to return no rows.

It seems, however, to optimize the view somehow and run the filter before the rank.

Is it this a standard behavior?

Is there a setting that controls this behavior?

Thanks in advance,

Marek

Add comment
10|10000 characters needed characters exceeded

  • Why don't you post the execution plan (cut out the irrelevant bits please - nobody wants to know the database name or the hostname etc.)? Also include the database software version while you're at it.

  • Get RSS Feed

1 Answer

  • Jan 13 at 01:40 PM

    Hi, filter is applied first before Rank node, that is default behavior. Applying filter in a node is a where clause on the CV, since your filter node is below the rank node, filter gets executed first. If you wanted to apply the filter after rank, you can apply the filter through 'VARIABLE' in the semantics node. Also, as Lars described, you can see in that execution plan diagram.

    Add comment
    10|10000 characters needed characters exceeded