mview creation got "duplicate column name" error
252122Jan 15 2007 — edited Jan 15 2007Hi,
This problem stops me!
Every time I create a materialized view with rowid and refresh fast, it explains ORA-00957: duplicate column name. Here is what I want to create:
CREATE MATERIALIZED VIEW person_race_mv
BUILD IMMEDIATE REFRESH FAST on commit enable query rewrite
AS SELECT pn.rowid "person_rid", re.rowid "race_id", pn.id, pn.first_name, pn.gender, pn.birth_date, re.id, re.ethnic_group
FROM person pn, race re WHERE pn.race_id=re.id;
If pn.id is not in select clause, then error didn't appear. But I have to have the id in select clause.
What is wrong here? Is ther a workaround? Your hint will be appreciated,