ORA-01791: not a SELECTed expression
SqlOracleSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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
.
Solution 2 - Sql
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.
Solution 3 - Sql
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
)