tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

Oracle/PLSQL: ORA-01785

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:

Oracle PLSQL

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;