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>
Does the table that you are using in the query have a primary key/Unique key ?
If so, you can have APEX use that column as the column instead of 'rowid' to uniquely identify a row in the table
Edited by: varad acharya on Nov 21, 2008 4:18 PM
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.
Skip, thank you for adding the additional description and information. I had been beating my head against a wall on this one. Solution worked perfectly.
Edited by: user9030474 on Jun 24, 2011 11:56 AM
Edited by: user9030474 on Jun 24, 2011 11:57 AM