Skip to Content
0

Rank node in calculation view not displaying the same rank as of rank sql window function values

Jun 05, 2017 at 01:24 PM

53

avatar image

Hi,

I am seeing different values when I used Rank node in calculation view when compared with the rank values displayed in sql script.

Below is my table having 5 records. Employee 101 has 3 records with date(1/1/2010) 2 records and date(1/1/2013) having 1 record. When applied rank function on the data in the table using partition by employee number and order by datefrom, the rank column is showing same value'1' for first two records which is expected.

I am trying to replicate the same thing using Rank node in CV. But it shows different rank for top two records. Below screenshot shows the definition of rank node used in CV.

Below is the data preview of the view.First record has rank1 and second record has rank 2 which is not complying with the sql query output above.Instead of rank values being displayed as 1,2,3, I would expect to display it as 1,1,3(like in sql) as datefrom has same value first two records. Appreciate the quick response.

Am I missing something or is it supposed to work like this only?

image1.jpg (93.6 kB)
image2.jpg (221.6 kB)
image3.jpg (56.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Jun 06, 2017 at 05:01 AM
0

Both results are correct. The difference occurs, because the a calculation views rank node uses internally the ROW_NUMBER window function instead of the RANK window function you have used in your SQL statement.

You can verify this, by doing a select on the calculation view and analyze it using the Explain Plan or PlanViz functionality.

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded