I am having trouble creating an interactive report using a hierarchical query - which I need. The error reported is ORA-01446 indicating that APEX is wrapping this in another query trying to get a ROWID. It does work as an SQL report but not as an interactive one causing the loss of functionality.
Has anybody experienced this before or know anyway around it.
To illustrate, try the following from any SQL editor:
+Create a tes
create table scratch (a number, p_a number);
Execute this query - this should run fine in SQL but fails in APEX for ORA-01446*
select * from scratch
connect by prior a = p_a;
Execute the same query wrapped with a ROWID query in SQLPLUS and you get the same error as in APEX interactive report*
select rowid from
select * from scratch
connect by prior a = p_a
Actual error message:
1 error has occurred <ul><li>The report query needs a unique key to identify each row. The supplied key cannot be used for this query. Please edit the report attributes to define a unique key column. ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.</li>
Thank you both so much for your input. Both worked great. I did the following with success:
Based on Scott suggestion, initially, I disabled the single-row view and that allowed me to enter the query in interactive mode.
Then based on Varad suggestion, I took it a little further and added an alias rowid column (from the inner joined table in the from-clause query). I re-enabled the single-row view and specified this aliased rowid and it works well still.
Thanks for suggesting the solution. This has been giving me fits while developing my first Apex application. I wasn't able to figure out when I would get the error or how to fix it. I do need the single row views as an application "standard". Since it took me a while to figure out how to do what you suggested, for others, here's specifically what I did.
In an interactive report linking several tables, with subqueries counting child entities with outer joins, I added as the first column in the SELECT statement "acr.rowid MY_ROWID,", where the primary table alias name is "acr".
I also added "acr.rowid MY_ROWID," to the GROUP BY list.
Then in the Report Attributes > Link Column, I linked to a single row view, chose Unique Column, and entered MY_ROWID and everything worked fine.
Tried it in another report that used UNION, and it worked there, too.
I may have run into trouble using lower case in various places. I'm still getting used to how Apex and Oracle SQL are case sensitive some places.