.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Subquery returned more than 1 value.

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hello, I need a gander at this query to figure out what i'm doing wrong. update Performance set pmpg = (select PMPG from Performance_Calculator where EFFDT_Converted = (select CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)) and Performance.DR_CODE = Performance_Calculator.FW_OWN group by Performance_Calculator.FW_OWN, Performance_Calculator.PMPG); basically the performance calculator, in the subquery, does return around 320 rows of data.  I thought the where clause would match up the rows of data and insert the pmpg into the performance table but instead I get subquery returned more than  1 value.  This is true but only if the performance_calculator.fw_own = performance.dr_code wasn't there.  I'm confused on this... any suggestions?

View Complete Post

More Related Resource Links

Subquery returned more than 1 value

SELECT (SELECT fare_amount FROM fare_tab WHERE (class_code = 'ECO')) AS Economy, (SELECT fare_amount FROM fare_tab AS f2 WHERE (class_code = 'TOU')) AS Tourist FROM fare_tab AS f1 I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can I solve this?

Error: 'SubQuery Returned More than 1 Value'

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1.  The code normally works however, every once in a while an exception is thrown: Code Snippet com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value.  This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?Stored Procedure: Code Snippet ALTER Procedure [dbo].[addRecord]@userID int,@itemID int,@info varchar(50),@comment varchar(50),@output int outputASDeclare @dateSubmitted datetimeset @dateSubmitted = getDate();--Insert the new record. THIS TABLE has an ID identity Primary Key --column that auto-increments.insert into RecordTable(UserPerson, Information, DateSubmitted)values (@userID, @info, @dateSubmitted);---Get the ID Assigned in the record table.  The Item table ---has a Foreign key on this column.Declare @assignedID  intset @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)/**Set the output parameter.*/set @output = @assignedID;--Now update the Item Table.insert into Item (ID, RecordID, Comment)values (@item,@assignedID, @comment);

subquery returned more than 1 value


I have an insert trigger that copies records to a history table when fired, based on certain criteria.   If a column is equal to a value, then I do some processing and ultimately insert the record into a history table.


If I do the below:


IF (select FIELDA from inserted) = 'ATEST'


this is ok unless there are multiple records, then I get

Msg 512, Level 16, State 1, Procedure ABCDEF, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


I can change the

IF (select FIELDA from inserted) = 'ATEST'

to include distinct


IF (select distinct FIELDA from inserted) = 'ATEST'


but, i don't know that the column will always be a distinct value, in which i think I would get

the same error as above.    I guess my question is, how do you get around this in a trigger?  Use a cursor?




Where to Filter the companies returned by the EF?



I am just starting with dynamic data. I have an enttity framework called UsersModel.edmx.

It returns a list of all companies in the tblCompany table. This list is really big.

I need to filter the list returned to all user controls on the client so that it only returns the company that have a CompanyTypeId= 2 or CompanyTypeId= 3 and company name != "".

1 - Is it possible to filter the company list returned on the server so that I can filter only once for all the controls that use that on the client?

My ideas was to do something like this using linq and lambda but I am not sure how:

return tblCompanys.Where(c => c.CompanyTypeId == 2 || c.CompanyTypeId == 3 && ).Where(c=>c.CompanyName != "").OrderBy(c => c.CompanyName).Distinct().OrderBy(c=>c.CompanyName);

2 - I have created a partial class and a metadata class and added the DisplayColumn attribute to use the company name and sort by company name ascending.

Could I use the code to filter the returned companies in this? where?

namespace MarsMedicalModel
    [DisplayColumn("CompanyName", "CompanyName")]
    public partial class t

Linq Subquery



I have a query that looks like this in SQL:

FROM StockMateriaPrima SMP
WHERE SMP.Fecha = (SELECT Max(Fecha)
                    FROM StockMateriaPrima SMP2
                    WHERE SMP2.Id = SMP.Id )

Can anyone tell me how to write this query using LINQ in VB.Net ?

Thanks !

how to evaluate nothing value returned by sql datareader in vb.net


When a sql query returns NULL , I can use the following code to evaluate:

While (dr.Read())
                If IsDBNull(dr(0)) Then
                End If
            End While


When a sql query returns nothing, neither IsDBNull nor is Nothing works.  I don't know why some sql queries return null while some return nothing when there are no data returned.  But anyway, can somebody tell me how to evaluate nothing value returned in sql datareader in vb.net?  When I put the cursor over signInfo variable in debug mode, I can see Nothing show as its value.  But the line does not work

if signInfo is Nothing then 


Thank you!

Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



The remote server returned an error: (405) Method Not Allowed.



{System.Net.WebException: The remote server returned an error: (405) Method Not Allowed.
   at System.Net.WebClient.UploadFile(Uri address, String method, String fileName)
   at System.Net.WebClient.UploadFile(Uri address, String fileName)
   at System.Net.WebClient.UploadFile(String address, String fileName)
   at Test.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\WebSite1\Test.aspx.cs:line 35}

Windows application, automated process will run on local user system...
trying to upload a file to website folder. website address:

System.Net.WebClient wc = new WebClient();
wc.UploadFile(website address, "c:/1.pdf");

Getting error from above line.. Am doing something wrong??
I dont want to use ftp or share folder logic..
is there any other way to upload the files to server??

Problem passing custom object returned by the same webservice among two separate projects

I am running into casting issue with this scenario: 1. I made a custom object lets call it EmployeeObject.  2. The Employee object is wrapped in the EmployeeWebservice.  3. I have a client application project lets call it EmployeeClientApplication and a class library called DoSomeWorkClassLibrary.  Both EmployeeClientApplication and DoSomeWorkClassLibrary reference the EmployeeWebService and need to be able to pass the EmployeeObject back and forth.  4. The EmployeeClientApplication also references the DoSomeWorkClassLibrary. 5. The employeeObject in the EmployeeClientApplication becomes EmployeeClientApplication.EmployeeWebService.EmployeeObject. 6. The employeeObject in the DoSomeWorkClassLibrary becomes DoSomeWorkClassLibrary.EmployeeWebService.EmployeeObject. 7. The DoSomeWorkClassLibrary has a method called DoSomeWork that takes an EmployeeObject. When I try to pass employeeObject from EmployeeClientApplication to DoSomeWorkClassLibrary by doing something like this: DoSomeWorkClassLibrary.DoSomeWork(EmployeeClientApplication.EmployeeWebService.EmployeeObject), I am getting Unable to convert EmployeeClientApplication.EmployeeWebService.EmployeeObject to DoSomeWorkClassLibrary.EmployeeWebService.EmployeeObject.  I have searched high and low to fix this issue, I have tried changing the namespace of the reference.cs file of the webservice in

Deserialization issue with returned from web service

Hi, I have a VS 2008 C#  client using a proxy generated by the Service Reference tool from a schema for a (prob Java?) ASMX web service.  I got serialisation errors when I tried connecting to the service around<xs:date> date fields, and on inspecting the incoming XML discovered the date format was yyyy-mm-dd hh:mm:ss.sss.   So 2 questions really.. 1) Is the schema I've been supplied with incorrect? 2)Is there a way to work around this, apart from manually editing the schema to xs:datetime and regenerating the classes?   Thanks MJ

Integer Value of 80 being returned as 20480

I'm writing an applicaiton that lists all of the ports of existing processes on the system.  However the system is returning the value of 20480 as the value for port 80.  I've found a couple of articles that would indicate that the discrepancy is because of the "endianess" of the underlying system and that reversing the byte order would resolve the problem.  However when I convert the return value to an array of bytes the values show up as 0,80,0,0.  Even reversing the order of the bytes wouldn't return the appropriate value of 80. Specifically I'm calling into the iphlpapi.dll using the following function [DllImport("iphlpapi.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]         private static extern int GetExtendedTcpTable(IntPtr pTcpTable, ref int dwOutBufLen, bool bOrder, int dwFamily, TCP_TABLE_CLASS dwClass, int dwReserved); Here's some additional code from the listing this.m_TcpTable.Add(new TcpEntry(entry.dwState, entry.dwRemoteAddr, entry.dwRemotePort, entry.dwLocalAddr, entry.dwLocalPort, entry.dwProcessId)); public TcpEntry(TcpState state, UInt32 remoteAddr, int remotePort, UInt32 localAddress, int localPort, int processID)         {             this.m_State = state; &nb

[SSRS 2008 R2]Report Builder Access error-The remote server returned an error: (401) Unauthorized.

Hello,        I am able to login Web Service URL and report manager using computer name and report builder is also working fine.  http://RPTSRV/reportserver   (RPTSRV is report server name. web service url working fine. all accessible) http://RPTSRV/reports  (report manager working fine and all accessible ,report builder too) It means I am able to access everything locally right? http://rs.test.abc.com/reportserver  (web service url working fine. all accessible)  http://rs.test.abc.com/reports    (Report manager working fine and all accessible accept report builder)(I carefully checked the link and its taking rs.test.abc.com/reportserver.....)     when I click on reportbuilder button in report manager it shows me error dialog box with following error log!! I tried from local and remote computer too but gives the same error both side. I am not sure but I am just thinking is this double hop kerberos security issue? Please help me to solve this problem. I am finding solution from last 4 days. Reporting Service configuratin tool settings Web Serivice URL -  all set to default Report Manager URL - all set to default Environment - Distributed deployment Server1(report_srv) --Windows server 2003, Installed sql server 2008R2 reporting service,

Sum With SubQuery

Hi people i am looking for This solution SELECT   funcionarioId, (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem i have this Query and it is Working Well and the result is: FuncionarioId | Value 1                   3010.00 2                   6000.00 1                   NULL 2                   50000.00 3                   2556.00 2                   300.00 4                   NULL  What i want is to SUM this SUM(VALUE) for each FuncionarioId, i dont have that column, those values is calculated from (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem. Thanks

error HRESULT E_FAIL has been returned from a call to a COM component

hello every one i see this error some times when i designed the page and close the program and came back.but this error do not stop debugging the page and hide all controls is my page and i cant use smart tag and see the controls.what should i do?

The remote server returned an error: (550) File unavailable (e.g., file not found, no access).

Hi.   I have read the other threads regarding this but they do not solve my problem. All i am trying to do is upload a file from the webserver onto another server via FTP. But i get the above error and am at my wits end as to why.   below is the code:   Dim fs As New FileStream("H:\Visual Studio 2005\Websites\Website1\testfile.txt", FileMode.Open) Dim filecontents(fs.Length) As Byte fs.Read(filecontents, 0, fs.Length)   Dim remotePath As String = "ftp://elonwactd.uk.ml.com/apps/files/IPBHVGTRADE/QA5/in/testfile.txt" Dim ftp As FtpWebRequest = FtpWebRequest.Create(remotePath) ftp.Credentials = New System.Net.NetworkCredential("user1", "passwd1") ftp.KeepAlive = False ftp.UseBinary = True   ' (have tried UploadFile as well instead of UploadFileWithUniqueName) ftp.Method = WebRequestMethods.Ftp.UploadFileWithUniqueName     fs.Close()   ftp.GetRequestStream.Write(filecontents, 0, filecontents.Length) ftp.GetRequestStream.Close() ftp.GetResponse.Close()     Below is the stack trace that i get. [WebException: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).]   System.Net.FtpWebRequest.SyncRequestCallback(Object obj) +321   System.Net.FtpWebRequest.RequestCallback(Object obj) +19   System.Net.CommandStream.Abort(Exception e) +163   System.Net.FtpWebRequest.FinishRequestStage(RequestStage stage) +45

How to write query with subquery more efficient

Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

The status code returned from the server was: 500

  I am getting the following eroor , when using the UpdatePanel of Ajax Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server.The status code returned from the server was: 500 To my strange ,the error is thrown not all the time , only in some rare cases which could not be found out . I tried to increase the AsyncPostBackTimeout to 300 which was 60 but i could n't  get the error down  installing the ajax set up in server is must or not ?  We just placed the ajax dlls in the server. Please can anybody help me with right solution.   Regards G Suresh Kumar         
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend