?
Solved

Looping through records

Posted on 2011-10-19
14
Medium Priority
?
199 Views
Last Modified: 2012-05-12
I have a table (PopulationBondsCashSummary) with data structered like this.  What I want to do is loop through each record and for each UserID evaluate the UserCount value.  If the @userCount is between 2 and 8 then set the @UserPctg value to the UserPctg value pulled from PopulationBondsCashSummary to set my TOP %.  

The error I am running into is on these two lines

SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary)
SET @USerPctg = (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary)

The error I am getting is "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."


USE [QA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
      DECLARE @Level Int
      SET @Level = 1
       
    DECLARE @numrows Int
      
      DECLARE @i int
      SET @i = 1

      SET @numrows = (SELECT COUNT(*) FROM PopulationBondsCashSummary)

      IF @numrows > 0
            WHILE (@i <= (SELECT COUNT(*) FROM PopulationBondsCashSummary))
            BEGIN
                        DECLARE @UserCount Int
                        SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary)
                        --PRINT @UserCount;      
      
                        DECLARE @USerPctg Dec
                        SET @USerPctg = (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary)                        
                        --PRINT @UserPctg      
                        
                        SELECT  UserID, UserCount, USerPctg
                        FROM    PopulationBondsCashSummary

                                    SELECT TOP (CASE  
                        --            --Level 1
                                          WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN @USerPctg                  
                                          
                                    END) PERCENT
                                          ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                    
                                    FROM    
                                          PopulationBondsCash_Temp            
                                    ORDER BY
                                          NEWID()
                                    SET @i = @i + 1       
            END            
Table sample
0
Comment
Question by:evanburen
  • 7
  • 7
14 Comments
 
LVL 3

Expert Comment

by:ajcheung78
ID: 36994482
Three things I see based on the way you have structured this.

First, looks like you need to store a variable that has the userid:

DECLARE @UserID VarChar(50)
 @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)

Second, modify your query for setting the @UserCount as follows:

SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)

Thirrd, modify your query for setting the @USerPctg as follows to use a windowing function and the userid:

SET @USerPctg = SELECT SUM(USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = @UserID)

0
 
LVL 3

Expert Comment

by:ajcheung78
ID: 36994530
Correction on last statement - should be something like this.

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = @UserID) AS x GROUP by x.UserID
0
 

Author Comment

by:evanburen
ID: 36994793
Hi

I'm seeing an error 'Incorrect syntax near the keyword 'SELECT'. on this line:

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = 5) AS x GROUP by x.UserID       

USE [QA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
      DECLARE @Level Int
      SET @Level = 1
       
    DECLARE @numrows Int
      
      DECLARE @i int
      SET @i = 1

      SET @numrows = (SELECT COUNT(*) FROM PopulationBondsCashSummary)

      IF @numrows > 0
            WHILE (@i <= (SELECT COUNT(*) FROM PopulationBondsCashSummary))
            BEGIN
                        DECLARE @UserCount Int
                  
                        DECLARE @UserID VarChar(50)
                      SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)
      
                        DECLARE @USerPctg Dec
                        SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = 5) AS x GROUP by x.UserID                         
                        --PRINT @UserPctg      
                        
                        SELECT  UserID, UserCount, USerPctg
                        FROM    PopulationBondsCashSummary

                                    SELECT TOP (CASE  
                        --            --Level 1
                                          WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN @USerPctg                  
                                          
                                    END) PERCENT
                                          ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                    
                                    FROM    
                                          PopulationBondsCash_Temp            
                                    ORDER BY
                                          NEWID()
                                    SET @i = @i + 1       
            END            
            

            
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Expert Comment

by:ajcheung78
ID: 36995611
Sorry - try this:

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) )
0
 
LVL 3

Expert Comment

by:ajcheung78
ID: 36995619
Ok one more time:

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) ) x
0
 

Author Comment

by:evanburen
ID: 36995840
Thanks, but still no luck.  Incorrect syntax near ')' on this line

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)) x
0
 
LVL 3

Expert Comment

by:ajcheung78
ID: 36995937
I guess I am not a very good visual debugger - try this:

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)) x
0
 

Author Comment

by:evanburen
ID: 36999413
I appreciate the help and it's not erroring out on the same line any longer but the overall query still isn't producing what I need.  This is what I have now.

My end goal is to produce a select statement like this but substituting the TOP percent based on the number of records for each userID and the
@userPctg value I'm pulling from another table.  Maybe my code needs a complete rewrite.  Thanks


SELECT TOP 10 PERCENT
ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                    
FROM    
PopulationBondsCash_Temp            
ORDER BY
NEWID()

            
USE [QA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	DECLARE @Level Int
	SET @Level = 1
       
    DECLARE @numrows Int
	
	DECLARE @i int
	SET @i = 1

	SET @numrows = (SELECT COUNT(*) FROM PopulationBondsCashSummary)
	
	IF @numrows > 0
		
		WHILE (@i <= @numrows)
		BEGIN
				DECLARE @UserCount Int
			
				DECLARE @UserID VarChar(50)
			    SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE @numrows = @i)
	
				DECLARE @USerPctg Dec
				SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) x)  				
					
				
				SELECT  UserID, UserCount, USerPctg
				FROM    PopulationBondsCashSummary
                
						SELECT TOP (CASE  
						--Level 1
							WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN @USerPctg
							WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN @USerPctg
							WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN @USerPctg
							Else
								100
						END) PERCENT 
							ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID						
						FROM    
							PopulationBondsCash_Temp		
						ORDER BY 
							NEWID()
							PRINT @UserID
						SET @i = @i + 1 	
		END

Open in new window

0
 
LVL 3

Expert Comment

by:ajcheung78
ID: 37000041
Can you provide some sample data and the expected results?
0
 

Author Comment

by:evanburen
ID: 37001571
Cash sample.png above contains the data in the table PopulationBondsCashSummary.  Desired Results
0
 

Author Comment

by:evanburen
ID: 37001609
Here is the data in PopulationBondsCash_Temp.   The UserCount field doesn't necessarily have to be in the desired results.  The number of records for each UserID should be based on the TOP @UserPctg value from  PopulationBondsCashSummary.   Thanks again.


 PopulationBondsCash_Temp
0
 
LVL 3

Accepted Solution

by:
ajcheung78 earned 2000 total points
ID: 37003349
I apologize that I did not do this earlier but I have been simply trying to correct your syntax errors assuming that your logic is sound.  

Looking a bit closer I see that even with your examples I cannot understand what you are trying to accomplish and how you arrived at the numbers in the desired results.

For instance how are you getting 0.03125 for the UserPctg and why are there multiple records for each UserID.

Can you provide just the data for one full example - e.g. for the ANSON-PRITCHARD BRENDAN record in its entirety and also provide the business reasons for what you are doing.

Also, this snippet of code makes no sense to me - what are you trying to accomplish here:
SELECT TOP (CASE  
                        --            --Level 1
                                          WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN @USerPctg
                                          WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN @USerPctg                  
                                          
                                    END) PERCENT 

Open in new window


Based on what you have stated so far and your original comments I would rewrite your code to start as follows and then add the logic you need to get the desired results.

 
DECLARE @UserID VarChar(50)

DECLARE c_PopulationBondsCashSummary CURSOR FOR
SELECT UserID FROM PopulationBondsCashSummary

OPEN c_PopulationBondsCashSummary

FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID

WHILE @@FETCH_STATUS = 0

BEGIN
	
	DECLARE @USerPctg Dec
	SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) x)  

	-- NEED SOME LOGIC HERE	
	
	FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID
	
END 

CLOSE c_PopulationBondsCashSummary
DEALLOCATE c_PopulationBondsCashSummary

Open in new window

0
 

Author Comment

by:evanburen
ID: 37017684
Thank you for your continued help. This is what I am trying to accomplish.  I need to get a
random sample (NEWID()) of records for each userID based on the total percentage of records in
PopulationBondsCash_Temp.  In the screenshot cashsample.png, KING LORRAINE has 512 records in
PopulationBondsCash_Temp which represent .115 precent of the records in that table.  
As a result, I need my random of sample of records for this userID to also be .115% of the total from
PopulationBondsCash_Temp.   PopulationBondsCashSummary is cashsample.png which is just used to hold
the percentage of records each user should be assigned.

So if the total number of records in PopulationBondsCash_Temp is 20, then I need to pull 8% of the
20 records for ANSON-PRITCHARD, 11% for KING LORRAINE, 8% for DUBOIS RAYMOND, 10% for BRIGANTE MATTHEW etc.

I hope this makes sense
0
 

Author Comment

by:evanburen
ID: 37020160
I was able to get it working with your cursor code.  Thanks!

DECLARE @UserID VarChar(500)

DECLARE @Level Int
SET @Level = 2

DECLARE @UserCount Int
SET @UserCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp)

DECLARE @PopSize Int
SET @PopSize = (
CASE
--Level 1
 WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN 2
 WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN 2
 WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN 3
 --Level 2
WHEN @Level = 2 AND @UserCount BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND @UserCount BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND @UserCount BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND @UserCount BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND @UserCount BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND @UserCount BETWEEN 16 AND 25 THEN 8
END
)

DECLARE @i int
SET @i = 1

DECLARE c_PopulationBondsCashSummary CURSOR FOR
SELECT UserID FROM PopulationBondsCashSummary

OPEN c_PopulationBondsCashSummary

FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID

WHILE @@FETCH_STATUS = 0 AND @i <= @Popsize
BEGIN

	DECLARE @USerPctg Float
	SET @USerPctg =  (SELECT USerPctg AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)  
	    
    DECLARE @TOPPopSize Int
    SET @TOPPopSize = CAST(@Popsize * @USerPctg AS Int)
       
   SELECT  UserID, UserCount, USerPctg
   FROM    PopulationBondsCashSummary

                SELECT TOP (CASE  
                --Level 1
                    WHEN @Level = 1 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
                    WHEN @Level = 1 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
                    WHEN @Level = 1 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize                      
                    --Level 2
					WHEN @Level = 2 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
					WHEN @Level = 2 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
					WHEN @Level = 2 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize
					--Level 3
					WHEN @Level = 3 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
					WHEN @Level = 3 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
					WHEN @Level = 3 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize			 
                      
                END) 
                      ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                                   
                FROM    
                      PopulationBondsCash_Temp                       
                WHERE UserID = @UserID
				
                ORDER BY 
                      NEWID()

	FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID
	SET @i = @i + 1	
END 

CLOSE c_PopulationBondsCashSummary
DEALLOCATE c_PopulationBondsCashSummary 

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question