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 +"')";
cmdread = new OracleCommand(strreg,dbcon);
readergnumber = cmdread .ExecuteReader();
while (finalread.Read())
{
Session["Technumber"]=finalread[" Amount"].ToString();
}
|
0 |
|
Mobzam
6/4/2008 7:47:57 AM
asp.net.oracle
2269 articles. 
0 followers.
3 Replies
1881 Views
From this site: http://ora-01427.ora-code.com/
| ORA-01427: | single-row subquery returns more than one row |
| Cause: | The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row. |
| Action: | Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved |
Clearly, the query: SELECT tempcode FROM specialstudent WHERE custid='"+ AreaNO +" is returning more than one row so the equal sign doesn't make sense.
Either change the sub query to return one row or use the IN key word.
Steve Wellens
My blog
|
|
0 |
|
SGWellens
6/4/2008 3:16:06 PM
Actual I want the sub query to return more rows.
So where should I play IN??
|
|
0 |
|
Mobzam
6/4/2008 3:39:38 PM
Try this -
string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno IN (SELECT tempcode FROM specialstudent WHERE custid='"+ AreaNO +"')";
Hope it helps.
-Manas
=======================================
If this post is useful to you, please mark it as answer.
|
|
0 |
|
yeotumitsu
6/4/2008 6:31:55 PM
Similar Artilces:
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 unionselect 'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA...
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...
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...
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!!! ...
How to read a dataset one row by one row?
I know you can use DataSet after close sql connection. I want to something after getting dataset, read from one row by one row, just like while datareader.read() Can I read dataset just like this? Thanks in advance! Justin Is this what you're looking for (from the Tips and Tricks Section of ASPNet101.com):http://aspnet101.com/aspnet101/tips.aspx?id=175David WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer&...
Crashes when a subquery returns more than one row
I had abandoned the idea of using ASE but my boss forced me to take a second look this labor-day weekend, and so I did. I found that latest EBF 16737 crashes, not the whole server, but the process, when a subquery return more than one row. This is my sql declare @tempcode varchar(15), @flag1 bit, @codclient int, @firstmin money set @tempcode='12127532626', @flag1=0, @codclient=127, @firstmin=10.00 select isnull(provrates_x.ipaddress, isnull((select ipaddress from provprefix p where p.id_prov=provider.id_prov ),provider.ipaddress)) from (select ipaddress,id_prov,code...
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...
Reduce rows displayed but putting 3 rows in one row
Hi all, I'm using PB6.5. I have an SQL DW that fetch some information from the DB but should display only two columns: a code and a value. Also they are displayed in the trailer of a group if that's actually important. In order to reduce displayed rows count, i would like to display each 3 rows in one row(Code,Value,Code,Value,Code,Value). The question is how can i do that?. I've tried to use computed fields and in the second and third pair values to use code[1],value[1],code[2],value[2] but only the code[1],value[1] are OK(Showing next row's data), the third ...
Returning n rows, setting start row, total number of rows
Hi, I'm try to find out how ado.net can perform some of the things i am able to do in ado. Here is the scenario: I will be searching a table that may have 1000's of rows. I want to be able to dictate how many rows (page size) to return and what the starting row number is and be able to find out how many total rows would be returned by the query. Using old ado recordsets I was able to get the recordset and get the count or all the rows (.recordcount - ormaybe it was .rowcount) and then jump to the starting position (.absoluteposition). How can I do the equivalent in .net? From loo...
Embedding rows into a single row?
Dear All,I need to display a page that shows a data grid, that’s fine. The slight trouble is that each item (a row) on the grid may have 1 or many action items associated with it.So what I’d like to do is display the grid and the rows as normal but embed the 1 or many items in the last column of the grid in each row as needed. Just like splitting some cells into many cells horizontally in an HTML table.How can I achieve this? Any ideas? Are you familiar with using Template columns with DataGrid? You can simply add a Template column that contains nearly anything you want in terms...
tow or more rows in a single row
Guys, I have one senario. Create table Client_Table ( ID int, SUB int, Name varchar(30), Status varchar(10) ) go insert into Client_Table select 9001,4,'Gail','Active' Select 9001,1,'Mark','Active' union all select 9001,2,'Will','InActive' union all select 9001,4,'Gail','Active' union all select 9002,1,'chals','Active' union all select 9003,1,'ken','Active' union all select 9003,2,'dave','Active' union all select 9004,1,'peter','Active' ...
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 unionselect 'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA...
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...
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...
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!!! ...
How to read a dataset one row by one row?
I know you can use DataSet after close sql connection. I want to something after getting dataset, read from one row by one row, just like while datareader.read() Can I read dataset just like this? Thanks in advance! Justin Is this what you're looking for (from the Tips and Tricks Section of ASPNet101.com):http://aspnet101.com/aspnet101/tips.aspx?id=175David WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer&...
Crashes when a subquery returns more than one row
I had abandoned the idea of using ASE but my boss forced me to take a second look this labor-day weekend, and so I did. I found that latest EBF 16737 crashes, not the whole server, but the process, when a subquery return more than one row. This is my sql declare @tempcode varchar(15), @flag1 bit, @codclient int, @firstmin money set @tempcode='12127532626', @flag1=0, @codclient=127, @firstmin=10.00 select isnull(provrates_x.ipaddress, isnull((select ipaddress from provprefix p where p.id_prov=provider.id_prov ),provider.ipaddress)) from (select ipaddress,id_prov,code...
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...
Reduce rows displayed but putting 3 rows in one row
Hi all, I'm using PB6.5. I have an SQL DW that fetch some information from the DB but should display only two columns: a code and a value. Also they are displayed in the trailer of a group if that's actually important. In order to reduce displayed rows count, i would like to display each 3 rows in one row(Code,Value,Code,Value,Code,Value). The question is how can i do that?. I've tried to use computed fields and in the second and third pair values to use code[1],value[1],code[2],value[2] but only the code[1],value[1] are OK(Showing next row's data), the third ...
Returning n rows, setting start row, total number of rows
Hi, I'm try to find out how ado.net can perform some of the things i am able to do in ado. Here is the scenario: I will be searching a table that may have 1000's of rows. I want to be able to dictate how many rows (page size) to return and what the starting row number is and be able to find out how many total rows would be returned by the query. Using old ado recordsets I was able to get the recordset and get the count or all the rows (.recordcount - ormaybe it was .rowcount) and then jump to the starting position (.absoluteposition). How can I do the equivalent in .net? From loo...
Embedding rows into a single row?
Dear All,I need to display a page that shows a data grid, that’s fine. The slight trouble is that each item (a row) on the grid may have 1 or many action items associated with it.So what I’d like to do is display the grid and the rows as normal but embed the 1 or many items in the last column of the grid in each row as needed. Just like splitting some cells into many cells horizontally in an HTML table.How can I achieve this? Any ideas? Are you familiar with using Template columns with DataGrid? You can simply add a Template column that contains nearly anything you want in terms...
tow or more rows in a single row
Guys, I have one senario. Create table Client_Table ( ID int, SUB int, Name varchar(30), Status varchar(10) ) go insert into Client_Table select 9001,4,'Gail','Active' Select 9001,1,'Mark','Active' union all select 9001,2,'Will','InActive' union all select 9001,4,'Gail','Active' union all select 9002,1,'chals','Active' union all select 9003,1,'ken','Active' union all select 9003,2,'dave','Active' union all select 9004,1,'peter','Active' ...
Web resources about - single-row subquery returns more than one row - asp.net.oracle Antagonistic Actions of Msx1 and Osr2 Pattern Mammalian Teeth into a Single Row
Mammals have single-rowed dentitions, whereas many nonmammalian vertebrates have teeth in multiple rows. Neither the molecular mechanism regulating ...
Mammals have single-rowed dentitions, whereas many nonmammalian vertebrates have teeth in multiple rows. Neither the molecular mechanism regulating ...
The Creator Of 'Super Smash Bros.' Once Worked 13 Months In A Row Without A Single Day Off... each player can do. Sakurai worked on the previous installment of "Smash Bros.," called "Super Smash Bros. Melee," for 13 months in a row without ...
Watch Every Single Episode Of The Simpsons, In A RowHere is a PSA for all of you fine Kotaku readers: starting today at 10am Eastern, FXX is broadcasting every single episode of The Simpsons , ...
Data Shaping in Google Refine – Generating New Rows from Multiple Values in a Single Column... one, two or three columns populated per student. If we wanted one row per student per course, we could just map the three columns onto a single ...
Resources last updated: 11/29/2015 6:13:36 AM