Need help with treeview report - error ORA-01427 - single-row subquery returns more than one row

Hai All, I hav a treeview report query in which most of the time it works perfectly without any error

And at times i received the ORA-01427 - single-row subquery returns more than one row. Not sure why this is not working with certain dataid. The query is as below,

select parentid "parent-id", dataid "Objid", level, decode(subtype,612,'VT',202,'P',144,'Doc',0, 'F',1,'-->','Other') "Type",
substr(lpad('____________________________________________________________________________________________________',5*(level-1))||d1.name,0,100)
"Object",nvl(decode(d1.dataid,d1.dataid,(select dv.datasize/1024 from dversdata dv where dv.version = d1.versionnum and dv.docid = d1.dataid)),0)
"Size in Kb", nvl(d1.childcount,0) "ChildCount", d1.createdate "Created", decode(d1.userid,d1.userid,(select k1.name from kuaf k1
where k1.id = d1.userid )) "Owner", d1.modifydate "ModifyDate",d1.versionnum "Number of versions"
from dtree d1 connect by prior d1.dataid = abs(d1.parentid)
start with d1.dataid = #######

Find more posts tagged with

Comments

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Sugaindra,

There must be some inconsistency or ambiguity in your database or at least within the rows you are selecting.

When you get the error, one of the two nested selects has returned more than 1 row.

Either

select dv.datasize/1024 from dversdata dv where dv.version = d1.versionnum and dv.docid = d1.dataid

select k1.name from kuaf k1 where k1.id = d1.userid

When it happens, resubmit the statement after removing ONE of those constructed columns with the inner select.

If it does not fail, now, then the one you removed was the culprit. If it still fails, the one you left in is where to look.

The underlying problem is that you need to add in additional join tests.

If you look at the definition of each of the indexes on the tables you are using you will see what combination of columns have unique constraints.

I suspect the issue with your data comes from the DVersData table.

The index DVERSDATA_PRIMARY is on columns DOCID, VERSION and VERTYPE and is defined as Unique.

Therefore if you had a rendition version of a document version, you will get two rows back in your query.

You might avoid this if you added AND VerType is NULL so that you get the main version for a document.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 February 21, Tue 08:58
To: eLink Recipient
Subject: Need help with treeview report - error ORA-01427 - single-row subquery returns more than one row

Need help with treeview report - error ORA-01427 - single-row subquery returns more than one row

Posted by s.krishnakumaran@shell.com (krishnakumaran, Sugaindra) On 02-21-2012 03:56

Hai All, I hav a treeview report query in which most of the time it works perfectly without any error And at times i received the ORA-01427 - single-row subquery returns more than one row. Not sure why this is not working with certain dataid. The query is as below, select parentid "parent-id", dataid "Objid", level, decode(subtype,612,'VT',202,'P',144,'Doc',0, 'F',1,'-->','Other') "Type",
substr(lpad('____________________________________________________________________________________________________',5*(level-1))||d1.name,0,100)
"Object",nvl(decode(d1.dataid,d1.dataid,(select dv.datasize/1024 from dversdata dv where dv.version = d1.versionnum and dv.docid = d1.dataid)),0)
"Size in Kb", nvl(d1.childcount,0) "ChildCount", d1.createdate "Created", decode(d1.userid,d1.userid,(select k1.name from kuaf k1
where k1.id = d1.userid )) "Owner", d1.modifydate "ModifyDate",d1.versionnum "Number of versions"
from dtree d1 connect by prior d1.dataid = abs(d1.parentid)
start with d1.dataid = #######

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Sugaindra_krishnakumaran

Thanks .. I shall try with the and vertype is null .... shall try and let you know the outcome.