ORA-01791 not a SELECTed expression

Hi fellas,

ORA-01791 not a SELECTed expression is kind of an untraceable error when i had came across in a first place. Oracle form which has a LOV (List of Values) pops up an error "ORACLE ERROR : Unable to read from List of Values" when click on the LOV button or press CTRL+L key combination. 

I haven't come across the exact reason for this but this has something to do with the database changes (Not like package changes or table structure changes but due to db version changes or rack changes) since the relevant query runs fine in all the existing 10g and 11g databases except a particular database which had a major change few dates back.

The relevant query is something like,

SELECT DISTINCT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Problem occurred because of the combination of the DISTINCT keyword with the ORDER BY keyword. To resolve this,

• Remove the DISTINCT keyword if there is a trigger available in relevant to eliminate entering duplicate data in to the table.
• Select all the columns which are using for ORDER BY clause along with the needed columns (In this scenario, you don't have to remove the DISTINCT keyword).

The query will be like,

SELECT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

or

SELECT DISTINCT receipt_no, r_amount, creation_date
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Happy coding...:)