How to resolve ORA-01785 Error Message in Oracle database

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

Description

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

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

Cause

You tried to execute a SELECT declaration that included a ORDER BY clause that referenced a column wide variety that did now not correspond to a legitimate 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 legitimate column numbers from your SELECT list. Remember that the first object in your SELECT listing is column 1, the 2nd 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 objects in your SELECT list, you can reference column 1 or column 2, however no longer column three The following SQL statement would be correct:

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