I have three tables as a master, child and grandchild. Simplified something like this:
CREATE TABLE TABLE1
(
ID NUMBER(10) NOT NULL,
TIME_STAMP DATE NOT NULL,
COL1 VARCHAR2(64 CHAR) NOT NULL
)
CREATE TABLE TABLE2
(
ID NUMBER(10) NOT NULL,
TIME_STAMP DATE NOT NULL,
TABLE1_ID NUMBER(10) NOT NULL,
COL2 VARCHAR2(64 CHAR) NOT NULL,
)
ALTER TABLE TABLE2 ADD (
CONSTRAINT TABLE2_FK
FOREIGN KEY (TABLE1_ID)
REFERENCES TABLE1 (ID))
/
CREATE TABLE TABLE3
(
ID NUMBER(10) NOT NULL,
TIME_STAMP DATE NOT NULL,
TABLE2_ID NUMBER(10) NOT NULL,
COL3 VARCHAR2(255 CHAR) NOT NULL
)
ALTER TABLE TABLE3 ADD (
CONSTRAINT TABLE3_FK
FOREIGN KEY (TABLE2_ID)
REFERENCES TABLE2 (ID))
/
In a larger query I have these three tables join in an inline view like this:
SELECT * FROM (
SELECT *
FROM table3 tbl3
JOIN table2 tbl2
ON tbl3.table2_id = tbl2.id
JOIN table1 tbl1
ON tbl2.table1_id = tbl1.id
WHERE tbl2.col2 = 'SOME_CODE'
AND tbl1.col1 = 'SOME_CODE'
AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD')
) WHERE time_stamp < :query_date
My problem was that I did not specify which of the time_stamp the reference in the where clause. Surprisingly enough I didn’t get any error but instead the database decided to use column table1.time_stamp! My first question is there any reason why I don’t get ‘ORA-00918: column ambiguously defined’? Took me some time to found the problem but when located then easy corrected by specifying in the select of the inline view what columns I’m interested in in this case tbl3.time_stamp. When testing I did fount that if I included two of the time_stamp in the columns list then I as expected will get the ORA-00918.
Please help me out, am I missing something here or is there some issue (bug or feature) with the inline view in Oracle 11?
Interesting what if we try to get column names in the query using dbms_sql.describe procedure we can see three similar name "time_stamp" and "id":
SQL> declare
2 c int;
3 clm dbms_sql.DESC_TAB;
4 c_n int;
5 begin
6 c := dbms_sql.open_cursor;
7 dbms_sql.parse(c => c
8 , statement => 'SELECT * FROM ( SELECT * '||
9 'FROM table3 tbl3 JOIN table2 tbl2 ON tbl3.table2_id = tbl2.id '||
10 'JOIN table1 tbl1 ON tbl2.table1_id = tbl1.id WHERE tbl2.col2 = ''SOME_CODE'''||
11 ' AND tbl1.col1 = ''SOME_CODE''' ||
12 ' AND tbl3.time_stamp > TO_DATE(''20130901'',''YYYYMMDD'')) where time_stamp < sysdate'
13 , language_flag => dbms_sql.native);
14 dbms_sql.describe_columns(c => c
15 , col_cnt => c_n
16 , desc_t => clm
17 );
18 for i in 1..c_n loop
19 dbms_output.put_line(clm(i).col_name);
20 end loop;
21 dbms_sql.close_cursor(c);
22 end;
23 /
ID
TIME_STAMP
TABLE2_ID
COL3
ID
TIME_STAMP
TABLE1_ID
COL2
ID
TIME_STAMP
But if you come from JOIN syntax to joins in WHERE clause you can get ORA-00918:
SQL> SELECT * FROM (
2 SELECT *
3 FROM table3 tbl3
4 , table2 tbl2
5 , table1 tbl1
6 WHERE tbl2.col2 = 'SOME_CODE'
7 AND tbl1.col1 = 'SOME_CODE'
8 AND tbl3.time_stamp > TO_DATE('20130901','YYYYMMDD')
9 AND tbl3.table2_id = tbl2.id
10 AND tbl2.table1_id = tbl1.id
11 )
12 where time_stamp < sysdate
13 /
where time_stamp < sysdate
*
error in line 12:
ORA-00918: column ambiguously defined
So seems it's just one of the many bugs coming from JOIN syntax.