Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with the below procedure Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 8:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:25 PM
Points: 2, Visits: 9
From: "Mishra, Abhinash, Vodafone UK, Partner" <abhinash.mishra@vodafone.com>
To: Abinash Mishra1/India/IBM@IBMIN,
Date: 11/15/2012 08:06 AM
Subject: proc



USE [R33HypInfa]
GO
create procedure DF012_Validation
AS
BEGIN
update [R33HypInfa].[dbo].[DF012_HFM_Staging]
set ICP=(select B.ICP
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Icp='VS001');
update [R33HypInfa].[dbo].[DF012_HFM_Staging]
Set Custom1=(select B.Custom1
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Custom1 in(select distinct Base_custom1 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C1]
where B.Custom1 in (Select Custom1 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C1] )));
set Custom2=(select B.Custom2
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Custom2 in(select distinct Base_custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2]
where
B.Custom2 in (Select Custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2] )));
END
GO

When I execute this below error is encountered

Error-:

Msg 512, Level 16, State 1, Line 1
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.
Post #1384952
Posted Wednesday, November 14, 2012 10:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
As you can guess from the error....one of your queries is returning more than a single value. But I won't be able to help you much just by looking at the code.....so you could either provide DDL and some sample data which would depict your problem and then perhaps we could correct the code for you.

Otherwise, you can just replace the "=" signs before the sub queries with "IN". But, that is if your data permits that as it could create results that you are not trying to do and might update more rows than actually you intend to update.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1384978
Posted Thursday, November 15, 2012 1:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 858, Visits: 2,318
update [R33HypInfa].[dbo].[DF012_HFM_Staging]
set ICP=
(select B.ICP
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A
,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Icp='VS001');

the way this is written explains why you have duplicate values, as I'm willing to bet money that the select returns more than one B.ICP value if run independantly of the update.

what is it you are trying to achieve?


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1385004
Posted Thursday, November 15, 2012 3:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Yes see this query can give you more then one record here . look into this query
select B.ICP
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Icp='VS001'



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1385027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse