Oracle rownum

'rownum' is an Oracle column that has the sole intension of numbering rows in the result set. There are a lot of blogs and article about it.

I happened to work with it today. I have a view that has a UNION in it. In order for NHibernate to return the correct row I needed to add the rownum. This is what I came up with

create or replace view EMPLOYEE_NAME as
select rownum, fname, lname
from employee
UNION
select rownum, fname, lname
from employee
inner join department on employee.id = department.employee_id;

The result set did not have unique rownum. I was baffled and not sure what went wrong there. Apparently if you have a union between to select statement, they need to clubbed together and the rownum needs to be on the result of the union.

The correct way to do this would be

create or replace view EMPLOYEE_NAME as

select rownum, e* from

(select fname, lname 

from employee

UNION 

select fname, lname 

from employee 

inner join department on employee.id = department.employee_id);