Skip to Content

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


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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jun 06, 2017 at 05:01 AM

    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.


    Add comment
    10|10000 characters needed characters exceeded