SQL Server Performance Forum – Threads Archive
Subquery returned more than 1 value
—————————————————————————————<br />– Transformation of Sales_Item_History <br />—————————————————————————————<br />IF EXISTS(SELECTName <br /> FROM dbo.sysobjects <br /> WHEREName= ‘pr_Trn_Sales_Item_History'<br /> ANDType= ‘P’)<br />BEGIN<br />DROP PROCEDURE pr_Trn_Sales_Item_History <br />END<br /><br />GO<br /><br />CREATE PROCEDURE pr_Trn_Sales_Item_History <br />AS<br />/*****************************************************************************************<br />Procedure Namepr_Trn_Sales_Item_History <br />DescriptionProcedure for transformation of Sales_Item_History table <br />Modification Log<br />Version No.Date AuthorModification<br />0.0031-Jan-2007 AshishInitial Version<br />0.0102-Feb-2007 AshishDone the Modifications to bring the SP in Sync with<br />the Design Document.<br />******************************************************************************************/<br />SET NOCOUNT ON <br /><br />DECLARE @i_Batch_NumINTEGER <br />DECLARE @i_Error_NumINTEGER<br />DECLARE @gdegatiINTEGER <br /> <br />[email protected]_Error_Num = 0<br /><br />SET @i_Batch_Num = 14<br />SET @gdegati = (SELECTGl_Distrib_Evnt_Gl_Amt_Type_Id <br /> FROMGl_Distrib_Evnt_Gl_Amt_Type <br /> WHEREGl_Distrib_Evnt_Cd = ‘CR'<br /> ANDGl_Amt_Type_Cd= ‘R’)<br />UPDATESales_Item_History<br />SETSales_Item_History.Amt_Pd = (SELECT SUM(-1*ST.Amt)<br />FROM Sales_TxnST,<br /> Sales_ItemSI,<br />Sales_Item_HistorySIH<br />WHEREST.Sales_Item_Id= SI.Sales_Item_Id<br />ANDST.Gl_Distrib_Evnt_Gl_Amt_Type_Id= @gdegati<br />ANDSIH.Sales_Item_Id= SI.Sales_Item_Id<br />GROUP BYST.sales_item_id)<br />RETURN0<br /><br /><br />– Error Logging<br /><br />Err_pr_Trn_Sales_Item_History: <br /> INSERT INTO Tbl_Logfile <br /> VALUES (‘pr_Trn_Sales_Item_History ‘,@i_Batch_Num , @i_Error_Num) <br /> RETURN 1000 <br />GO<br /><br /><br /><br />While Execution I get the error as follows[<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />!]<br />Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.<br />The statement has been terminated.<br /><br />Pls give me the reason where I went wrong.[<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />]<br /><br />Ashish Johri
Hi,
As the error suggests, SQL server is expecting one value and subquery is returning more than that. In below code you are using subquery and here it must be returning more values. UPDATE Sales_Item_History
SET Sales_Item_History.Amt_Pd = (SELECT SUM(-1*ST.Amt)
FROM Sales_Txn ST,
Sales_Item SI,
Sales_Item_History SIH
WHERE ST.Sales_Item_Id = SI.Sales_Item_Id
AND ST.Gl_Distrib_Evnt_Gl_Amt_Type_Id = @gdegati
AND SIH.Sales_Item_Id = SI.Sales_Item_Id
GROUP BY ST.sales_item_id) fire the subquery on its own without update and check the result
I think the following sub query is returning more than one value while populating the variable…
Check the line number in your error also…and run only select statement and see how many rows it returns… SET @gdegati = (SELECT Gl_Distrib_Evnt_Gl_Amt_Type_Id
FROM Gl_Distrib_Evnt_Gl_Amt_Type
WHERE Gl_Distrib_Evnt_Cd = ‘CR’
AND Gl_Amt_Type_Cd = ‘R’) MohammedU.
Moderator
SQL-Server-Performance.com
Thanks for useful comments. Specially MohammedU who came forward with one likely posibility. Regards,
Ashish Ashish Johri
]]>