I am trying to create a unique identifier for every row of a view. The view I have joins a lot of tables and therefore no one table's primary key will help me identify the row.
Doing a google search it looks like I may be able to achieve this by using rowid? But I'm not sure how to reference the view's rowid. Below is an example of how I envisioned rowid would work, but it obviously fails with an 'ambiguous column' error because I am not specifying a specific table for rowid.
Ex:
with v_someTable (select...),
v_anotherTable as (select blah, id from v_someTable where...),
v_yetAnotherTable as (select foo, id from v_someTable where...)
select distinct rowid, rt.key, v1.blah, v2.foo
from realTable rt
left join v_anotherTable v1 on v1.id=rt.id
left join v_yetAnotherTable v2 on v2.id=rt.id
I am trying to do this in a query and not a stored procedure. Any help would be appreciated!
Thanks!
My understanding is that a rowid refers to a row in a physical table, rather than a row in a result set (which is effectively what a view is).
To get a unique identifier for each row, you'd need to combine the primary keys of the tables that you're joining in some way.