I am having problems with the Row_Number function. I am using it to assign row numbers to records where a student has a grade of pass on a module and excluding failed modules (I want it to show a 0 as row number for failed modules). The problem is that when I try to use a condition, the report still assigns a row number to a failed module though it doesnt display it (it displays a 0 which I wanted it to show). The results appear as follows:
|4 (instead of 3)||ModuleD||Pass|
How can I make it skip assigning a row number to all failed modules? Please assist.
I have tried using the CASE statement and it gave me the same results as shown in the table above and also the CASE statement was showing an error on the report Data Model (Reports Builder) because I have linked queries. Is there any other function that can be used to get row numbers OR what do I need to include on the Row_number function to achieve what I want? Thanks.
It is tricky to use rownum. Because if we generate sequence no by select max(column)+1 from table name ..we can match the where condition and assign the incremented value to the PASS condition.
On the other hand rownum generates a sequence while it is fetching the records one by one so obviously you get the above result only So i will give you an idea to fetch the records of 'PASS' condition first.
Take a look at the below query and alter it and use it on your report.
select a,b,c from (
select 0 a,module b, grade c from table where grade = 'FAIL'
select rownum a,module b, grade c from table where grade = 'PASS' order by b)
order by a
So the above query generates row number only for the PASS condition.
Hope this helps...