java.sql.SQLException: ORA-01791: not a SELECTed expression - resolved

When the unique or distinct key word is used in the SELECT clause and at the same time an ORDER BY clause is attached to the end of a query, it sometimes returns the SQLException that says "not a selected expression" , remove either the ORDER BY clause or the unique/distinct key word from the query stops the exception.

The following two queries throw such a SQLException.

1. SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
      FROM person ORDER BY lastname, firstname;

2. SELECT unique personId, firstname, lastname, ssn
      FROM person ORDER BY lastname, firstname, middlename;

The real cause of the exception is that for the ORDER BY to work properly when a unique/distinct key word is used, it has to be able to clearly identify the columns put in the ORDER BY clause from the SELECT clause. If a column in the ORDER BY clause is missing or unable to be identified, the exception is generated.

In the above first query, both the lastname and firstname in the ORDER BY are not clearly specified in the SELECT clause. In the above second query, the middlename in the ORDER BY clause is missing from the SELECT clause.

If you don't mind of duplicate or incorrectly ordered results, you may either remove the unique/distinct key word or the column causing the problem from the ORDER BY clause. Otherwise, you need to modify your query to make it work.

The following modifications will fix the above two queries,   

1. SELECT unique personId, nvl (firstname, ' ') firstname, nvl (lastname, ' ') lastname, ssn
      FROM person ORDER BY lastname, firstname;

      OR

      SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
      FROM person ORDER BY  nvl (lastname, ' '), nvl (firstname, ' ');

2. SELECT personId, firstname, lastname, ssn FROM (
      SELECT unique personId, firstname, lastname, middlename, ssn
      FROM person ORDER BY lastname, firstname, middlename);

-------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.