drup drup - 9 days ago 4
SQL Question

ORA-01791: not a SELECTed expression

I need to fetch details from DB. Any thing wrong in my code?

SELECT DISTINCT FNAME, LNAME, MEMBERORG, DAYCOUNT, TIMESTAMP, COUNT(FNAME) AS total,(SELECT COUNT(*) FROM REPORT_VIEW_PAGE) AS tot
FROM REPORT_VIEW_PAGE
WHERE ID = '68' AND TYPE = 'node'
GROUP BY FNAME, LNAME, MEMBERORG, DAYCOUNT, TIMESTAMP
ORDER BY TITLE ASC


This giving me an error:

ORA-01791: not a SELECTed expression
01791. 00000 - "not a SELECTed expression"
*Cause:
*Action:
Error at Line: 6 Column: 10

Answer

The problem here is the ORDER BY column TITLE isn't selected in the DISTINCT query. Since DISTINCT is used, the SELECT query will try to group the resultset based on the selected columns.

ORDER BY column isn't selected here, it doesn't ensure the uniqueness on the resultset and hence it fails to apply ORDER BY.

Comments