ORA-01446 - cannot select ROWID from view with DISTINCT, GROUP BY, etc.
I have created a view that is different in the select clause.
When I try to select all records using "select * from view" I get the following error:
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.
I searched why this is happening and came up with this:
You tried to create a view that includes a ROWID in a SELECT statement, as well as a clause such as DISTINCT or GROUP BY. It is forbidden. Link
This is inconvenient because the select item in the view does not select the rowid and is not used in any other clause (where, order, etc.)
Any idea on this?
I cannot post the request myself, but I am posting a similar view. Here he is:
SELECT DISTINCT t1.c1 TABLE1_C1, t1.c2 TABLE1_C2, t1.c3 TABLE1_C3, t1.c4 TABLE1_C4, t1.c4 TABLE1_C4, t1.c5 TABLE1_C5, t1.c6 TABLE1_C6, t1.c7 TABLE1_C7, t1.c8 TABLE1_C8, t2.c1 TABLE2_C1, t2.c2 TABLE2_C2, t2.c3 TABLE2_C3, t2.c4 TABLE2_C4, t2.c5 TABLE2_C5, t3.c1 TABLE3_C1, t2.c6 TABLE2_C6, t4.c1 TABLE4_C1, t4.c2 TABLE4_C2, t4.c3 TABLE4_C3 FROM table1 t1 LEFT JOIN table2 t2 ON t1.c1 = t2.c7 left JOIN table4 t4 ON t4.c4 = t2.c1 LEFT JOIN table3 t3 ON (t2.c1 = t3.c2 AND t2.c8 = t3.c3 AND t2.c9 = t3.c4) WHERE (t2.cp5 = 0 or t2.cp5 is null) AND (t2.c3 = (SELECT MAX(c3) FROM table2 s_t2 WHERE s_t2.c3 LIKE t2.c3 AND s_t2.c7 = t1.c1 ) or t2.c3 is null)
source to share
Not sure what the OP is doing as there is no ROWID in the request. But it might help - just add the column alias:
SQL>select * from (select rowid from dual union select rowid from dual); select * from (select rowid from dual union select rowid from dual) * ERROR at line 1: ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc. SQL>select * from (select rowid as row_id from dual union select rowid from dual); ROW_ID ------------------ AAAAB0AABAAAAOhAAA 1 row selected.
source to share
Another possibility . After going through this error for the last 3 days, I have another combination of options that could cause this.
- your request is using ANSI joins and
- one or more related tables is a materialized view or table that has the QUERY REWRITE option
You may get Oracle error:
ORA-01445 : unable to select ROWID from or sample, no table join view with key preserved
Finally, we found that 2 tables in a large query set this option and threw this error. Permission has been granted to our schema for these two tables.
It took so long to determine what was the cause, because: 1) the tables belong to a different team; 2) the tables have granted QUERY REWRITE permission to our schema, but the query (compiled in daily work) "Will not work until 3 weeks later, after the system is updated.
The granting team revoked it, but the issue persists. Therefore, our short term work for daily work is as follows:
ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;
We are still wondering how to get the optimizer to re-parse the query to find out that we no longer have this grant, but may reboot the system.
While I understand that a materialized view creates a physical table, I also still ask myself why a table that is not part of mview would be granted this right.
source to share