ANSI Left Join Query With Text CONTAINS Operator Returns Ambiguous Column Error ORA-00918 (Doc ID 756711.1)

Last updated on JANUARY 19, 2012

Applies to:

Oracle Text - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 19-Jan-2012***

Symptoms

-- Problem Statement:

When running a ansi left join query with a contains or two contains clauses, although query has fully qualified table names, it returns an ORA-00918.
Same query works fine in 9iR2, for example 9.2.0.7 and 9.2.0.8.

Example of a query which fails with ORA-918 error:

SELECT DISTINCT EXTFEATUREID, QBE.GENOMEID, CONTIGNAME, FEATURETYPE, PRODUCT,
NAME, AD.Datafield
FROM QBERESULTSET QBE
INNER JOIN AnnotationData AD ON (AD.FeatureID = QBE.FeatureID AND
AD.LocationID = QBE.LocationID)
AND AD.Approval != 'Rejected'
LEFT JOIN AnnotationTypes AT ON(AT.AnnotationType = AD.AnnotationType)
LEFT JOIN AnnotationRules AR ON(AR.RulesSetName = AT.RulesSetName AND
AR.RulesSetID = AD.DataField)
WHERE QBE.FEATURETYPE IN ('CDS', 'GENE','TRNA','RRNA','MISC_RNA')
AND (CONTAINS(AD.DataField, 'sra') > 0 OR
CONTAINS(AR.fulldescription, 'sra') > 0)
/

SELECT DISTINCT EXTFEATUREID, QBE.GENOMEID, CONTIGNAME, FEATURETYPE, PRODUCT,
NAME, AD.Datafield
*
ERROR at line 1:
ORA-00918: column ambiguously defined


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms