Oracle / PLSQL: ORA-01785 Error Message

Learn the cause and how to resolve the ORA-01785 error message in Oracle.

Description

When you encounter an ORA-01785 error, the following error message will appear:

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

Cause

You tried to execute a SELECT statement that included a ORDER BY clause that referenced a column number that did not correspond to a valid column in your SELECT list.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite the SELECT statement so that the ORDER BY clause references valid column numbers from your SELECT list. Remember that the first item in your SELECT list is column 1, the second is column 2, and so on.

For example, if you had tried to execute the following SELECT statement:

SELECT supplier_id, supplier_name
FROM suppliers
ORDER BY 3;

You would receive the following error message:

Since there are only 2 items in your SELECT list, you can reference column 1 or column 2, but not column 3. The following SQL statement would be correct:

SELECT supplier_id, supplier_name
FROM suppliers
ORDER BY 1, 2;