November 14, 2012 at 8:03 pm
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.
November 14, 2012 at 10:40 pm
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.
November 15, 2012 at 1:49 am
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
November 15, 2012 at 3:01 am
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;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply