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
