DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01785 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

ORA-01785 is an Oracle error that occurs when the ORDER BY clause references a column or expression that is not valid within the context of a compound query using set operators (UNION, UNION ALL, INTERSECT, MINUS). Oracle restricts ORDER BY in such queries to column aliases defined in the first SELECT list or positional numeric indexes. This error is one of the most common mistakes developers encounter when working with multi-query set operations.

Top 3 Causes

1. Referencing a Table Column Name Directly in ORDER BY with Set Operators

When using UNION or other set operators, you cannot reference a specific table's column name in the ORDER BY clause. Oracle treats the combined result as a unified dataset, so individual table column references are not allowed.

-- This causes ORA-01785
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY employees.emp_name;  -- ERROR: table-qualified column not allowed

-- Fix: Use column alias from the first SELECT
SELECT emp_id AS id, emp_name AS name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY name;  -- OK: alias from first SELECT
Enter fullscreen mode Exit fullscreen mode

2. Using a Positional Index That Exceeds the SELECT List Count

If you use a numeric positional index in ORDER BY that is greater than the number of columns in the SELECT list, Oracle throws ORA-01785. For example, using ORDER BY 5 when only 2 columns are selected will fail immediately.

-- This causes ORA-01785 (only 2 columns selected)
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY 5;  -- ERROR: index 5 out of range

-- Fix: Use a valid index within column count
SELECT emp_id, emp_name FROM employees
UNION
SELECT dept_id, dept_name FROM departments
ORDER BY 2;  -- OK: sorts by second column (emp_name / dept_name)
Enter fullscreen mode Exit fullscreen mode

3. Using Functions or Expressions Directly in ORDER BY

Oracle does not allow functions like TO_DATE(), UPPER(), or arithmetic expressions directly in the ORDER BY clause of a compound set-operator query.

-- This causes ORA-01785
SELECT emp_id, hire_date FROM employees
UNION
SELECT dept_id, created_date FROM departments
ORDER BY TO_CHAR(hire_date, 'YYYY-MM-DD');  -- ERROR: expression not allowed

-- Fix: Wrap in inline view, then apply function in outer ORDER BY
SELECT id, ref_date
FROM (
    SELECT emp_id AS id, hire_date AS ref_date FROM employees
    UNION
    SELECT dept_id, created_date FROM departments
)
ORDER BY TO_CHAR(ref_date, 'YYYY-MM-DD');  -- OK: applied outside the set query
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

The most reliable fix is to wrap the entire set-operator query in an inline view and apply ORDER BY in the outer query. This approach eliminates virtually all ORA-01785 scenarios.

-- Universal safe pattern for complex UNION queries
SELECT *
FROM (
    SELECT
        emp_id      AS entity_id,
        emp_name    AS entity_name,
        salary      AS amount,
        hire_date   AS ref_date
    FROM employees

    UNION ALL

    SELECT
        dept_id,
        dept_name,
        budget,
        created_date
    FROM departments
)
ORDER BY amount DESC, ref_date;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always define explicit column aliases in the first SELECT of a UNION query.
Oracle uses the first SELECT block's aliases as the final result column names. Clear aliases make ORDER BY straightforward and prevent accidental ORA-01785 errors. Make this a mandatory code review standard for all set-operator queries.

Standardize the inline view wrapper pattern for all compound queries.
Adopt a team convention of always wrapping UNION/INTERSECT/MINUS queries inside an inline view, then handling ORDER BY, WHERE, and other clauses in the outer query. This pattern prevents ORA-01785, improves readability, and makes future maintenance significantly easier.

Related Errors

  • ORA-01789 – Result queries have different number of columns (column count mismatch in set operators)
  • ORA-01790 – Expression must have same datatype as corresponding expression (data type mismatch in UNION columns)
  • ORA-01786 – FOR UPDATE clause not allowed with set operators

📖 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.

Top comments (0)