SQL View with ID column

Question

I'm working with a SQL view that I created but I want to add in an ID column (identity seed) as the current one has none. How can I accomplish this in SQL View?

Answer 1

If there is no identity column in the underlying table, you can generate one with pseudo-columns.

In SQL server: SELECT ROW_NUMBER() OVER (ORDER BY FiscalYear, FiscalMonth), FiscalYear, FiscalMonth, ... FROM ... See http://msdn.microsoft.com/en-us/library/ms186734.aspx

In Oracle: SELECT ROWNUM, FiscalYear, FiscalMonth, ... FROM ... . In oracle, ROWNUM uses the order in the result set.