0

I have a trouble querying view in Oracle.

Here is the view (my_cool_view) definition: 

SELECT *
  FROM mview1 JOIN
       mview2 USING(col_id)

where mview1 and mview2 is materialized views. I can't access to definition of this materialized views. I've tried this query:

select r.title from my_cool_view r;

and got ORA-00918: column ambiguously defined error. I got this error for subset of columns in my_cool_view.

If I run this query:

select * from my_cool_view;

all works fine.

You are asking about desc my_cool_view in comments, sorry I can't provide schema information because it's confidential. The only I can say it contains about 80 columns, and only one (col_id) don't cause above error.

Do you have any ideas what is the case and how to fix it?

Thanks in advance.

Sorry for misled you, the first version of question was too bad.

Kai
  • 2,023
  • 7
  • 28
  • 49
  • 1
    you propably have column title defined twice in your view. Could you add description of it? ("desc my_cool_view" in SQL*Plus) – Petr Pribyl Oct 31 '12 at 10:39
  • I've just checked - column define once. What do you mean under description? – Kai Oct 31 '12 at 10:51
  • Can you update your question with the output of `desc my_cool_mview`? – Colin 't Hart Oct 31 '12 at 11:13
  • please post the ddl of materialized view. – Florin Ghita Oct 31 '12 at 11:14
  • Can you confirm that it's a materialized view and not an ordinary view? – Colin 't Hart Oct 31 '12 at 11:14
  • @Colin'tHart, Please, refer to my update. – Kai Oct 31 '12 at 11:20
  • @Colin'tHart, do you have thoughts, what is the cause of the problem? – Kai Oct 31 '12 at 12:26
  • Can you fix the question? Can you post the DDL of the view -- or at the very least a describe of the object -- as requested? – Colin 't Hart Oct 31 '12 at 12:29
  • Updated, hope this version is rather good. – Kai Oct 31 '12 at 12:45
  • @Kai mview1 and mview2 have both a column named `title`. This is obvious. You should at least choose the current answer as accepted. – Florin Ghita Oct 31 '12 at 13:52
  • @FlorinGhita,but when I do _select * from my_cool_view_ I have only one _title_ column (_title1_ is absent). So, it seems for me that title is not duplicated. Am I correct? – Kai Oct 31 '12 at 14:03
  • I don't know if you are correct. You should know this. Mview1 has a column `title`? Mview2 has a column `tile`. If yes, and because `your_cool_view` is `SELECT * FROM mview1 JOIN mview2 USING(col_id)` then you are not. The title is twice. – Florin Ghita Oct 31 '12 at 14:40
  • and change the view into 'SELECT * FROM mview1 JOIN mview2 on (mview1.col_id = mview2.col_id)'. `JOIN USING` has some debates with column name and aliases. – Florin Ghita Oct 31 '12 at 14:43
  • @FlorinGhita, I don't have access to code in mview1 and mview2, so I can't say exactly, if those tables both have _title_ column. In any case thanks a lot you for your help and suggestions. – Kai Oct 31 '12 at 15:05

1 Answers1

2

You probably have 2 columns called title from different tables/views that you've joined. If you select * then this will work as the duplicate title columns will get a different alias (like TITLE1). When you explicitly ask for one of the title columns the database needs to know which one you want and thus throws the error.

Just make sure that the title columns have a different alias:

   select EMP.TITLE EMP_TITLE, DEPT.TITLE DEPT_TITLE
   FROM   ...
diederikh
  • 25,221
  • 5
  • 36
  • 49
  • 1
    If you have a materialized view, then Oracle has created a table with *unique* column names. – Colin 't Hart Oct 31 '12 at 11:12
  • @Diedirik Hoogenboom, when I do _select * from my_cool_view_ I have only one _title_ column (_title1_ is absent). So, it seems that title is not duplicated. Am I correct? – Kai Oct 31 '12 at 14:05