need help with ORA-01427: single-row subquery returns more than one row error

the following select in oracle 10g gives an error , need some help thanks

 

select 'AGD' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCAGD' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGAGD'and DATA_OBJECT_ID is not null ) is not null

union

select
'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA,242,5) from SSRFMSC where SUN_DB='AGD' and SUN_TB = 'BUD' and (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SA' || substr(KEY_FIELDS,1,1) || 'FLDGAGD' and DATA_OBJECT_ID is not null ) is not null

union

select 'CXX' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCCXX' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGCXX' and DATA_OBJECT_ID is not null ) is not null

union

select 'CXX', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA,242,5) from SSRFMSC where SUN_DB='CXX' and SUN_TB = 'BUD' and (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SA' || substr(KEY_FIELDS,1,1) || 'FLDGCXX' and DATA_OBJECT_ID is not null ) is not null

union

select 'DEM' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCDEM' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGDEM' and DATA_OBJECT_ID is not null ) is not null

thanks

robby

0
robby32
6/25/2008 6:52:43 AM
asp.net.oracle 2269 articles. 0 followers. Follow

2 Replies
1787 Views

Similar Articles

[PageSpeed] 11

 

I'm not going to wade thru that much badly formatted code... Smile

The error you are getting is very straightforward.  In your select clause you have embedded queries that are supposed to supply a "column" value.

One or more of them is returning more than one row and that's not allowed.  You need to tighten up the where clauses on those queries in the select clause so that they only return one row.

If you need to, comment all but one of them out, fix it as needed, uncomment one more, lather, rinse, repeat.

 

0
david
6/25/2008 7:41:48 AM

now worries thanks ..

0
robby32
6/25/2008 10:24:30 AM
Reply:

Similar Artilces:

single-row subquery returns more than one row
 I want a subquery  to return more than  one value then  use those values to get more values from  the other table. But  I  am receiving the errors saying ORA-01427: single-row subquery returns more than one row. How can I avoid this and achieve  want I want? My sql and Csharp is below:dbcon.Open();            string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno=(SELECT tempcode FROM  specialstudent WHERE custid='"+ AreaNO +"')";    &...

UPDATE multiple ROWS with different Values with single statement ?- Multiple Rows in subquery- ORA-01427
Hi I'm hoping one of you kind folk can help with this. I want to UPDATE a field of  table with  the results of another query, (sub query), but my sub quesry contains a Group By and so returns multipel rows.UPDATE TABLE_ONE  SET TOTAL_SALES =(SUBQUERY) The Subquery:  Select COUNT(*) from TABLE_TWO where TRANSACTION_TYPE="SALE"  GROUP BY PERSON_ID QUERIES togetherUPDATE TABLE_ONESET TOTAL_SALES=(SELECT Count(*) FROM TABLE_TWO GROUP BY PERSON_ID)  ThisSubquery  return multiple rows and hence teh ORA-01427 error.Can anyone tell me how to loop thr...

Return single colum from multiple rows into one row?
Often in my apps I'll create a table in SQL to house different variables. I do this so that I don't end up with multiple tables just to store a list of phone types for example. What I mean is let's say I have a form that would store contact information one of the controls might be a phone number and then next to that I'd have a drop down that would list the different phone types like Phone, Mobile, Fax etc rather than hard coding a specific textbox for only phone or only the mobile number. This way the user can determine white type of contact number it is.  With th...

Help with Subquery cannot return more than one row
I'm sure there is something I am doing wrong, but I am completely at a loss to figure out what is going on here. I have three tables related in the following order... worksheet_parent <-- worksheets <-- worksheet_details worksheet_parent has 1 row, worksheets as 3 rows, and worksheet_details as 6 rows. Parent, Child, Grandchild. Standard PK<FK layout. We use this in an estimating portion of our product. If we need to handle a delete, all we have to do is delete the worksheet_parent row and it cascades through worksheets and worksheets cascades into worksheet...

Need help finding out if a row found after query. Return row count.
I am very new to .net/asp.net 2.0. I take the data in the textbox and submit it as a query to return the relevant dataset from the database. Then, to find out if a record (row)was found. I tried in C# but not work. SqlConnection myBarcode;SqlDataAdapter dapSelect;DataSet sqldata = new DataSet();myBarcode = new SqlConnection();string strSelect = "select barcode from Barcode where barcode = '" + TextBox1.Text + "'"; dapSelect = new SqlDataAdapter(strSelect, myBarcode);dapSelect.Fill(sqldata);if (sqldata != null)        {      ...

Help need in gridview to display one row initily and add the new row at runn time
Hi,    Am creating the Gridview in that i put to HTMLSelect control and i populate item value from the database in ItemTemplate. Now i want to display only one row initily with the two HTMLSelect Control and if user want new row the can add by click the addnew row button. How to go with this...? I dont know how to Display only one row initily and add the new row at the end of the item template at the run time can any one help me.   Thanks Hi Spider.net, The GridView rows collection's Count property is equals to the row number in it's datasource. If...

Inserting all rows of a datatable into Oracle Table in a single shot, not row by row using C#
Hi All... I have imported the data of CSV File into Datable using C#. Now i need to insert the content of DataTable to the table in Oracle Database in a single shot, without looping through the rows of the datatable. Is this Possible? Please help me out by providing the sample code. Below is sample code for Importing the CSV Data to Dataset string connString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;",Path.GetDirectoryName(filename)); string cmdString =string.Format("SELECT * FROM {0}", Path.G...

single row query rteturn more than one row
aSBnb3QgZm9sbG93aW5nIG1lc3NhZ2Ugd2hlbiBpIHJ1biBzcWwgc3RhdGVtZW50IGFzIHNx bA0KDQpzcWw9c2VsZWN0IHVwX3RpbWUgZnJvbSB1cHRpbWUgd2hlcmUNCiAgICAgICAgICAg ICBzdGFydGRhdGUgPSAoc2VsZWN0IEdSRUFURVNUKHN0YXJ0ZGF0ZSkNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBmcm9tIHVwdGltZSB3aGVyZQ0KICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIGhvc3RuYW1lID0gJ3R3aXN0ZXInKQ0KREJEOjpPcmFj bGU6OnN0IGV4ZWN1dGUgZmFpbGVkOiBPUkEtMDE0Mjc6IHNpbmdsZS1yb3cgc3VicXVlcnkg cmV0dXJucw0KbW9yZSB0aGFuIG9uZSByb3cgKERCRCBFUlJPUjogT0NJU3RtdEV4ZWN1dGUp IGF0IHVwZ2V0LnBsIGxpbmUgMzUuDQoNCk9SQS0wMTQyNzogc2luZ2xlLXJvdyBzdWJxdWVy eSByZXR1...

How do i get one row if the resultset returned has no rows.
Hi I have couple of CTE's which i want to ultimately insert into a temp table... and based on my condition some of the CTE's return some rows and some dont... but i want to have the CTE return atleast one row if there is no data in it.. I have tried doing the following but i am not getting the desired output    ;with cte_rpt4 as (select '4' a,recd_dt as approve_dt, prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt, claim_amt, rpt4, (Select count(distinct(Claim_mst_Id)) as crpt4 from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(c...

Error with Gridview when i delete the row that have only one row in gridview
 Dear all, i develop with asp.net 2005 and use gridview and set enable deleteing =true. I can delete record in gridview properly but when gridveiw has only one row and then i'm clike delete button , the error occurs :   Server Error in '/it' Application. Specified argument was out of the range of valid values.Parameter name: value Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentOutOfRangeExce...

Gridview Rows stretching in size when one row is returned
HiI hope this is a trival problem.My problem with the gridview is that when one row or only a few rows are returned to the gridview the spacing round the data in the row(s) increases. If there are many more rows, say 10 or more, the spacing is how I want it to be. I have tried setting the spacing for the rows in the properties of the row, but with no luck.Does any one have a way of fixing the row spaces regardless of the number of rows returned? Thanks Hi AndyMoireASP, It seems you have resolved the issue. If so, please post the solution and share with us. Thanks,Qin Dian TangMicro...

xml export rows as with template
Ive created a datawindow with a template. Now when i do a save as, it only returns one row? I am expecting all of the rows , using the template I have defined. > Ive created a datawindow with a template. Now when i do a > save as, it only returns one row? I am expecting all of > the rows , using the template I have defined. found the solution. Start Detail option!!! ...

Need help in return how many rows
HI All, I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that? This is my sp, and when it returns the data i also want it to return the how many rows are the result. CREATE procedure dbo.ph_getphonebookoption @country nvarchar(100), @company nvarchar(100), @office nvarchar(100) as select Ext, FName, LName, Title from phonebook where country =@country and company=@company and office =@office select count (*) GO Jessica ----------------...

Error: Select returns more than one row.
I'm trying to write a stored procedure that will end up taking several parameters and build an sql statement. I keep getting an error that says "Select returns more than one row". I can't find any reference in the documentation that says a select can only return 1 record so I must be doing something wrong. I would appreciate any suggestions. alter procedure "dba".sp_Score(@zipcode integer) result(COP_ID integer,COP_NAME char(100),PCT integer) begin declare @@sql varchar(500); set @@sql=STRING('SELECT COP_ID, SIMILAR(COP_POSTAL_CODE, '||@...

Web resources about - need help with ORA-01427: single-row subquery returns more than one row error - asp.net.oracle

Resources last updated: 2/4/2016 5:12:38 AM