ERROR:Subquery returned more than 1 value.Please help me

Question

ERROR:Subquery returned more than 1 value. This is not permitted when the subquery follows = !=  >= or when the subquery is used as an expression. DECLARE @Expiration DATETIME; DECLARE @Signeddate VARCHAR(50); DECLARE @TextMarketing VARCHAR(50); Select @Signeddate=(SELECT SCHEDULEDTIME FROM #TEMP_SB ); SET @Expiration=(DATEADD(DAY-1(DATEADD(YEAR1@Signeddate)))) If(@Signeddate BEGIN SET @TextMarketing='ACTIVE'; END else if(@Expiration is not null OR @Expiration BEGIN SET @TextMarketing='INACTIVE'; END UPDATE #TEMP_SB SET TEXT_MARKETING_STATUS=@TextMarketing

Pravin More · Answer

yes got it.....i think its for last line..............change it to prefix with set set @rowcount=@rowcount+1;

Pravin More · Answer

hi karthik error reason is clear that you assigning more than 1 record to @Signeddate n thats not allowed....... but their is 1 solution for your above logic...if i am not wrong ..you are going to update every record of #TEMP_SB according to SCHEDULEDTIME....if so then you have to excute your above logic for every row of #TEMP_SB in while loop like below...... for that modify your #TEMP_SB and add 1 more column of rownumer starting from zero(0) using RowNumber() function of sql server .......n execute you code in while loop...i modified your code...you will get idea from it DECLARE @Expiration DATETIME; DECLARE @Signeddate VARCHAR(50); DECLARE @TextMarketing VARCHAR(50); declare @Count int; declare @rownum int; set @rownum=0; select @Count =(SELECT Count(SCHEDULEDTIME) FROM #TEMP_SB ); while @rownum begin Select @Signeddate=(SELECT SCHEDULEDTIME FROM #TEMP_SB where rownumber=@rownum ; --this will give one record at a time SET @Expiration=(DATEADD(DAY-1(DATEADD(YEAR1@Signeddate)))) If(@Signeddate =@Expiration ) BEGIN SET @TextMarketing='ACTIVE'; END else if(@Expiration is not null OR @Expiration BEGIN SET @TextMarketing='INACTIVE'; END UPDATE #TEMP_SB SET TEXT_MARKETING_STATUS=@TextMarketing @rownum=@rownum+1; end

karthik parcha · Answer

Hi thank you its working Plz can u write a small query for me My requirement is Example;Example if the contract signed was on 2/3/2011 then it would be active through 2/2/2012. I used BETWEEN operator is this correct or not. i have to get the SIGNING_DATE i have to write the condition that the IF SIGNING_DATE is betwwen the oneyear the contract is active. This is my query SELECT @Signeddate=(SELECT T.SIGNING_DATE FROM #TEMP_SB T WHERE T.SIGNING_DATE BETWEEN T.SIGNING_DATE AND DATEADD(DAY-1(DATEADD(YEAR1T.SIGNING_DATE)))).... //Contract Signing Date + 1 year - 1 day SET @Expiration=(DATEADD(DAY-1(DATEADD(YEAR1@Signeddate)))) If(@Signeddate BEGIN SET @TextMarketing='ACTIVE'; END Plz help me

karthik parcha · Answer

Here iam getting error like incorrect syntac near rowcount plz help me DECLARE @Expiration DATETIME; DECLARE @Signeddate DATETIME; DECLARE @TextMarketing VARCHAR(50); DECLARE @Scheduled DATETIME; DECLARE @ScheduleCount int; DECLARE @rowcount int; SET @rowcount=0; --DECLARE @SignedCount int; --DECLARE @rowcount1 int; SELECT @ScheduleCount =(SELECT Count(SCHEDULEDTIME) FROM #TEMP_SB ); WHILE @rowcount BEGIN Select @Signeddate=(SELECT SCHEDULEDTIME FROM #TEMP_SB where ROW_NUMBER=@rowcount); SET @Expiration=(DATEADD(DAY-1(DATEADD(YEAR1@Signeddate)))) If(@Signeddate BEGIN SET @TextMarketing='ACTIVE'; END else if(@Expiration is not null OR @Expiration BEGIN SET @TextMarketing='INACTIVE'; END UPDATE #TEMP_SB SET TEXT_MARKETING_STATUS=@TextMarketing @rowcount=@rowcount+1; END

karthik parcha · Answer

Thank you so much awesome. i will work on it Thanks karthik

karthik parcha · Answer

Hi Select @Signeddate=(SELECT SCHEDULEDTIME FROM #TEMP_SB ); i have records like SCHEDULEDTIME some times returns NULL value and some times it returns more than one value based on the ID how to write query for that iam getting error when i used IN keyword incorrect syntax error iN

Mamta M · Answer

Check the outcome of this statement separately: SELECT SCHEDULEDTIME FROM #TEMP_SB Is it returning more than one row? I think yes that's why you're getting an error. So you need to give a WHERE clause to ensure that it returns ONLY one row because you are saying @Signeddate=(SELECT SCHEDULEDTIME FROM #TEMP_SB ); If you want SELECT SCHEDULEDTIME FROM #TEMP_SB to return multiple rows then your statement must be like: @Signeddate IN (SELECT SCHEDULEDTIME FROM #TEMP_SB ); ie you use IN instead of =.