CODEWITHSUNDEEP
sqloracle-database
Bablu Ahmed
Bablu Ahmed

Reputation: 5010

Getting Column Ambiguously Defined in Oracle SQL

In Oracle, when adding where clause with the below query, I get an error

Column ambiguously defined

Query:

SELECT
    t1.*, t2.*
FROM
    NM_APPLICATION t1
LEFT JOIN 
    NM_QUOTA t2 ON t2.QUOTA_ID = t1.QUOTA_ID
WHERE
    ACTIVE_FLAG = 'Y'
ORDER BY
    MERIT_POSITION DESC

Please help me

Upvotes: 0

Views: 75

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Most likely the issue here is that both tables NM_APPLICATION and NM_QUOTA have a column called ACTIVE_FLAG. The remedy is to decide which table's column you want to use, and then provide an alias to that table:

SELECT t1.*, t2.*
FROM NM_APPLICATION t1
LEFT JOIN NM_QUOTA t2
    ON t2.QUOTA_ID = t1.QUOTA_ID
WHERE t1.ACTIVE_FLAG = 'Y'        -- or t2.ACTIVE_FLAG
ORDER BY MERIT_POSITION DESC

By the way, if this answer be correct, then it might indicate a design problem that two tables in the same schema have a column which might be storing the same or similar data. So, you might want to rethink your table design.

Upvotes: 3

Indent
Indent

Reputation: 4967

You need prefix columns ACTIVE_FLAG or/and MERIT_POSITION by t1 or t2 alias if exists in both table

Upvotes: 3

Related Questions