Calculation view with rank and filter unexpected result

[Former Member]

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

[lbreddemann]

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.

[ssurampally]

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.