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 3 Answers
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.
Add the title column to your SELECT statement. When you're using DISTINCT, you must have all the columns from the ORDER BY in your SELECT statement as well.
-- correct SELECT DISTINCT a, b, c FROM tbl.x ORDER BY a,b; -- incorrect SELECT DISTINCT c FROM tbl.x ORDER BY a,b; The a and b columns must be selected.
SELECT DISTINCT FNAME, LNAME, MEMBERORG, DAYCOUNT, TIMESTAMP, total, tot FROM ( 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 ) ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoampuaWaBeHyOqKmaZWBmhHp9jKemrWWRYsCmuMScq56cXZrFsb7ErKqip54%3D