Geek asks and answers

Error with link in Oracle inline view column

I have three tables as a master, child and grandson. 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 see that these three tables join the inline view:

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

, , time_stamp where. , , table1.time_stamp! - , "ORA-00918: ? , , , , , tbl3.time_stamp. fount, time_stamp , , , ORA-00918.

, , - ( ) Oracle 11?

+3
2

, dbms_sql.describe, "time_stamp" "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

JOIN WHERE, 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

, , JOIN.

+1

, * , , time_stamp time_stamp, time_stamp_1, time_stamp_2.

where time_stamp =, , time_stamp table1.time_stamp.

sql ,

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')
+1

More articles:


All Articles