Cursor error:Subquery returned more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor error:Subquery returned more than 1 value

Author

Topic

hs123456
Starting Member

6 Posts

Posted - 2010-02-05 : 09:31:08

Help

I am using Cursors and inserting each record into temp table, but at line "SET @fName" I get the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help is appreciated.

DECLARE @submission NVARCHAR(MAX)
DECLARE @temp NVARCHAR(MAX)
DECLARE @fName NVARCHAR(MAX)
DECLARE @fValue NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #SplitValues (
fName NVARCHAR(MAX),
fvalue NVARCHAR(MAX)

)

DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
Select Replace(cast(MyTable.Submission as nvarchar(max)),'***', ';') from MyTable

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @submission

WHILE @@FETCH_STATUS = 0
BEGIN

WHILE len(@submission) > 0
BEGIN

SET @fName = (select substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1) from MyTable)

SET @fValue = (select substring(substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))), 0, charindex(';',substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))))) from MyTable)

INSERT INTO #SplitValues VALUES (@fName,@fValue)
SET @submission = (substring(@submission, 0, len(@submission) - len(@fValue)))

END

FETCH NEXT FROM db_cursor INTO @submission
END

CLOSE db_cursor
DEALLOCATE db_cursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:32:46

can you explain in simple english what you're trying to do with above code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:33:49

the reason for error is because

select substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1) from MyTable

returns multiple records. why are trying to assign this to variable? may be its a temporary table that you want

hs123456
Starting Member

6 Posts

Posted - 2010-02-05 : 09:42:39

This is what I am trying to do:

select all records from 1 column. It is in this format

1. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
2. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
3. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3

SET @fName will hold column name
SET @fValue will hold the column value

Insert the first 2 values into temp table: for eg. columnname1, columnvalue1

Loop through the while loop to get columnname2; columnvalue2 and then insert it.

So cursors should give me 1 record and I can do operations, but it throws the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

hs123456
Starting Member

6 Posts

Posted - 2010-02-05 : 09:54:26

can someone please suggest what I am doing wrong here and provide a solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:00:02

quote:
Originally posted by hs123456

This is what I am trying to do:

select all records from 1 column. It is in this format

1. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
2. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
3. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3

SET @fName will hold column name
SET @fValue will hold the column value

Insert the first 2 values into temp table: for eg. columnname1, columnvalue1

Loop through the while loop to get columnname2; columnvalue2 and then insert it.

So cursors should give me 1 record and I can do operations, but it throws the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

make use a parsing udf like below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

then do like


INSERT INTO YourTable
SELECT MAX(CASE WHEN ID%2 = 0 THEN Val ELSE NULL END),
MAX(CASE WHEN ID%2 <> 0 THEN Val ELSE NULL END) 
FROM dbo.ParseValues(yourcol,';') f
WHERE ID > 1
GROUP BY (ID/2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources