On 12/5/06, John Siracusa <[EMAIL PROTECTED]> wrote: > 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?
Do you have some code I can run on an Oracle 9 database to test this? What if the table aliases are ta, tb etc instead of t1, t2,...? -- Clayton ------------------------------------------------------------------------- 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
