It looks like you have to have uniquely named column names: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:768123480 57 (search for 918 on the page and you'll get to the right spot)
For some reason you can create a View with that query, but you can't retrieve data. -Kevin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Siracusa Sent: Tuesday, December 05, 2006 11:03 PM To: Rose-DB-Object Subject: [RDBO] ORA-00918 DBD::Oracle::db prepare failed: ORA-00918: column ambiguously defined. DBD ERROR: error possibly near <*> indicator at char 24 in: SELECT * FROM ( SELECT a.<*>*, ROWNUM oracle_rownum FROM ( SELECT t1.b1, t1.b2, t1.bits, t1.date_created, t1.fixed, t1.fk1, t1.fk2, t1.fk3, t1.flag, t1.flag2, t1.id, t1.last_modified, t1.name, t1.save, t2.k1, t2.k2, t2.k3, t2.name FROM scott.rose_db_object_test t1 JOIN scott.rose_db_object_other t2 ON (t1.fk2 = t2.k2 AND t1.fk1 = t2.k1 AND t1.fk3 = t2.k3) ORDER BY t1.id DESC ) a WHERE ROWNUM <= 10 ) WHERE oracle_rownum >= 9 Supposedly, ORA-00918 is caused by this: "A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO." The solution: "Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.)" (From http://ora-00918.ora-code.com/) I don't see how that error applies at all in the query above, and neither does this poor guy who has the same problem: http://ora-00918.ora-code.com/msg/28663.html A work-around for RDBO is to turn on unique_aliases ("t1.foo as t1_foo"), but yeesh. Any better ideas, Oracle experts? -John ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
