Oracle ORA-00960 Error: Causes and Solutions Complete Guide

ORA-00960: Ambiguity in Column Naming in SELECT List

ORA-00960 occurs when Oracle cannot determine whether a name in the ORDER BY or GROUP BY clause refers to an actual table column or a column alias defined in the SELECT list. This typically happens when a defined alias matches an existing column name in one of the queried tables, or when multiple joined tables share the same column name without proper table qualifiers. The fix is almost always straightforward: use explicit table aliases and unique column aliases.

---

Top 3 Causes

1. Column Alias Conflicts with an Actual Column Name

When you name a derived column with the same alias as a real column in the table, Oracle gets confused in the ORDER BY clause.

-- Problematic query (ORA-00960)
SELECT employee_id,
       salary * 1.1 AS salary   -- alias 'salary' same as real column
FROM   employees
ORDER BY salary;                 -- ambiguous: real column or alias?

-- Fixed: use a distinct alias
SELECT employee_id,
       salary * 1.1 AS adjusted_salary
FROM   employees
ORDER BY adjusted_salary;

-- Alternative: reference by position
SELECT employee_id,
       salary * 1.1 AS salary
FROM   employees
ORDER BY 2;   -- unambiguous positional reference

2. Multiple Tables with Identical Column Names in a JOIN

When two or more joined tables share column names and no table alias is specified, Oracle cannot resolve which table's column to use.

-- Problematic query (ORA-00960)
SELECT department_id,    -- exists in both employees and departments
       first_name,
       department_name
FROM   employees e,
       departments d
WHERE  e.department_id = d.department_id
ORDER BY department_id;  -- which table's department_id?

-- Fixed: qualify every column with a table alias
SELECT e.department_id,
       e.first_name,
       d.department_name
FROM   employees   e
JOIN   departments d ON e.department_id = d.department_id
ORDER BY e.department_id;

3. Duplicate Alias Names Across CTEs or Subqueries

Using the same alias in multiple CTE blocks and then referencing it in the outer query creates ambiguity.

-- Problematic CTE (ORA-00960)
WITH dept_data AS (
    SELECT department_id,
           AVG(salary) AS avg_sal    -- alias: avg_sal
    FROM   employees
    GROUP BY department_id
),
emp_data AS (
    SELECT employee_id,
           department_id,
           salary * 1.05 AS avg_sal  -- same alias: avg_sal!
    FROM   employees
)
SELECT d.department_id, d.avg_sal, e.avg_sal
FROM   dept_data d
JOIN   emp_data  e ON d.department_id = e.department_id
ORDER BY avg_sal;  -- ORA-00960: which avg_sal?

-- Fixed: use unique aliases across CTEs
WITH dept_data AS (
    SELECT department_id,
           AVG(salary) AS dept_avg_salary
    FROM   employees
    GROUP BY department_id
),
emp_data AS (
    SELECT employee_id,
           department_id,
           salary * 1.05 AS emp_adjusted_salary
    FROM   employees
)
SELECT d.department_id,
       d.dept_avg_salary,
       e.emp_adjusted_salary
FROM   dept_data d
JOIN   emp_data  e ON d.department_id = e.department_id
ORDER BY d.dept_avg_salary DESC;

---

Quick Fix Solutions

Scenario	Fix
Alias matches real column name	Rename alias to something unique
Ambiguous column in multi-table join	Prefix all columns with table alias
Duplicate alias across CTEs	Use descriptive, unique aliases per CTE
Cannot rename alias	Use positional ORDER BY 2 syntax

---

Prevention Tips

Always qualify every column with a table alias.
Even in single-table queries, get into the habit of writing e.salary instead of just salary. When the query later evolves into a join, you will never introduce ORA-00960.

-- Good habit even for single-table queries
SELECT e.employee_id,
       e.first_name,
       e.salary
FROM   employees e
WHERE  e.department_id = 10
ORDER BY e.salary DESC;

Adopt a naming convention for derived columns.
Prefix or suffix computed and aggregated columns to ensure they never clash with base table columns. For example, use total_, avg_, calc_ prefixes or _pct, _rank, _ratio suffixes consistently across your codebase. Enforce this in code reviews and, where possible, integrate a SQL linter such as SQLFluff into your CI/CD pipeline to catch ambiguous references automatically before they reach production.

---

Related Errors

• ORA-00918 (column ambiguously defined): Similar to ORA-00960 but triggered in WHERE or JOIN conditions rather than the SELECT list. Both are resolved by adding table alias qualifiers.
• ORA-00957 (duplicate column name): Raised when the same column name appears twice in a CREATE TABLE or SELECT ... INTO statement, which can be a root cause leading to ORA-00960.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.