Solved

Complex SQL Statement using Cursors

Posted on 2011-03-16
7
230 Views
Last Modified: 2012-05-11
I have an application which needs to update a large number of items using information recovered from multiple table.  I have shown the stored procedure below which is fairly well commented hopefully.

 
CREATE PROCEDURE [dbo].[usp_chassishistory_updatedistance]
	@chassisHistoryID int,
	@newDistance real
AS

BEGIN
	DECLARE @distanceRun int
	DECLARE @componentID int
	DECLARE @checknamesID int
	DECLARE @checknamesName Nvarchar(128)
	
	-- Update the components run in the session indicated by the chassis history record to reflect the new distance 
	-- covered in their race and test distances
	UPDATE COMP SET 
		COMP.icomponent_life_left = (COMP.icomponent_life_left + CHASHIST.ichassishistory_distance - @newDistance),
		COMP.icomponent_life_test_left = (COMP.icomponent_life_test_left + CHASHIST.ichassishistory_distance - @newDistance),
		COMP.icomponent_life_weighted = (COMP.icomponent_life_weighted 
										+ (CHASHIST.ichassishistory_distance * COMPHIST.icomponenthistory_factor_value)
										- (@newDistance * COMPHIST.icomponenthistory_factor_value))
	FROM icomponent COMP
		LEFT JOIN icomponenthistory COMPHIST ON COMPHIST.icomponent_id = COMP.icomponent_id
		LEFT JOIN ichassishistory CHASHIST ON COMPHIST.ichassishistory_id = CHASHIST.ichassishistory_id
	WHERE COMP.icomponent_id IN 
	(
		SELECT icomponent_id FROM icomponenthistory COMPHIST 
			LEFT JOIN ichassishistory CHASHIST ON COMPHIST.ichassishistory_id = CHASHIST.ichassishistory_id
		WHERE COMPHIST.ichassishistory_id = @chassisHistoryID
	)

	-- Ensure that any checks are updated for all components involved in the specified session
	-- We need to use a cursor to loop through the components as otherwise it is simply too complex!
	DECLARE COMPONENT_CURSOR CURSOR FAST_FORWARD FOR
		SELECT icomponent_id FROM icomponenthistory WHERE icomponenthistory.ichassishistory_id = @chassisHistoryID

	-- Open the cursor and fetch the next component ID
	OPEN COMPONENT_CURSOR
	FETCH NEXT FROM COMPONENT_CURSOR INTO @componentID
	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- We can only update one check at a time so need another nested cursor to loop through the checks
		DECLARE CHECK_CURSOR CURSOR FAST_FORWARD FOR
			SELECT ichecknames_id ,ichecknames_name FROM ichecknames

		-- Open the cursor and fetch the next component ID
		OPEN CHECK_CURSOR
		FETCH NEXT FROM CHECK_CURSOR INTO @checknamesID, @checknamesName	
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			-- Now we update the checks for this specific component
			SET @distanceRun = isnull(((SELECT SUM(ichassishistory.ichassishistory_distance) As NewDistance FROM ichassishistory
				INNER JOIN icomponenthistory ON ichassishistory.ichassishistory_id = icomponenthistory.ichassishistory_id
			WHERE icomponenthistory.icomponent_id = @componentID
				AND ichassishistory.ichassishistory_sessionname <> ''
				AND ichassishistory.ichassishistory_date >= 
				(SELECT MAX(ichassishistory.ichassishistory_date) FROM ichassishistory
					INNER JOIN icomponenthistory ON ichassishistory.ichassishistory_id = icomponenthistory.ichassishistory_id
				 WHERE icomponenthistory.icomponent_id = @componentID
					AND ichassishistory.ichassishistory_description LIKE @checknamesName + ' Reset%'))) ,0)

			-- Now that we have the distance run since the last reset of the specified check we need to actually set the 
			-- 'to next' value for this check to take into account the distance run as calculated
			UPDATE icheckvalues SET icheckvalues_life_left = icheckvalues_life_new - @distancerun ,icheckvalues_test_left = icheckvalues_test_new -  @distancerun
				WHERE icomponent_id = @componentID AND ichecktype_id = @checknamesID			
		
			-- Next Check
			FETCH NEXT FROM CHECK_CURSOR INTO @checknamesID, @checknamesName	
		END

		-- Close and de-allocate checks cursor
		CLOSE CHECK_CURSOR
		DEALLOCATE CHECK_CURSOR



		FETCH NEXT FROM COMPONENT_CURSOR INTO @componentID
	END

	CLOSE COMPONENT_CURSOR
	DEALLOCATE COMPONENT_CURSOR

	-- Now update the chassis history record to reflect the new distance
	UPDATE ichassishistory SET ichassishistory_distance = @newDistance WHERE ichassishistory_id = @chassisHistoryID

END

Open in new window


The first part of the SP updates items in the icomponent table and works fine.  The second part of the query does the following:-

Search the icomponenthistory table and return all components (icomponent_id) which have been created for the specified chassis history record (ichassishistory_id)

Each component may have a number of entries in the icheckvalues table - 1 per check defined in the ichecknames table.  For each of these checks we calculate a distance using the 'SET @distanceRun' query - and use this to update the icheckvalues table.

I have currently written this using nested cursors but was wondering if there was a better/more efficient/clearer way of doing this query?
0
Comment
Question by:ChrisMDrew
  • 4
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35155873
yes don't have the cursors

it isn;t complex...

just use a temp table and do setprocessing
CREATE PROCEDURE [dbo].[usp_chassishistory_updatedistance]
	@chassisHistoryID int,
	@newDistance real
AS

BEGIN
	
	
	-- Update the components run in the session indicated by the chassis history record to reflect the new distance 
	-- covered in their race and test distances
	UPDATE COMP
           SET COMP.icomponent_life_left = (COMP.icomponent_life_left 
                                         + CHASHIST.ichassishistory_distance - @newDistance)
              ,COMP.icomponent_life_test_left = (COMP.icomponent_life_test_left
                                              + CHASHIST.ichassishistory_distance - @newDistance)
              ,COMP.icomponent_life_weighted = (COMP.icomponent_life_weighted 
	          			     + (CHASHIST.ichassishistory_distance
                                             * COMPHIST.icomponenthistory_factor_value)                        						                     - (@newDistance * COMPHIST.icomponenthistory_factor_value))
	  FROM icomponent COMP
	  Inner JOIN (select * 
                        from icomponenthistory
                       where ichassishistory_id = @chassisHistoryID
                     ) COMPHIST
            ON COMPHIST.icomponent_id = COMP.icomponent_id
	  LEFT outer JOIN ichassishistory CHASHIST 
            ON COMPHIST.ichassishistory_id = CHASHIST.ichassishistory_id
	
	
	
			-- Now we update the checks for this specific component
			SELECT n.ichecknames_id,co.icomponent_id
                              ,SUM(ch.ichassishistory_distance) As distancerun
                          into #temp
                          FROM ichassishistory as ch
			 INNER JOIN icomponenthistory as co
                            ON ch.ichassishistory_id = co.ichassishistory_id
                          cross join  ichecknames as n

			WHERE ch.ichassishistory_id=@chassishistoryid
			  AND ch.ichassishistory_sessionname <> ''
			  AND ch.ichassishistory_date >= 
				(SELECT MAX(ichassishistory.ichassishistory_date) 
                                   FROM ichassishistory as xch
				  INNER JOIN icomponenthistory as xco
                                     ON xch.ichassishistory_id = xco.ichassishistory_id
				  WHERE xco.icomponent_id = co.icomponent_ID
				    AND xch.ichassishistory_description LIKE n,ichecknames_name + ' Reset%')

			-- Now that we have the distance run since the last reset of the specified check we need to actually                         -- set the 
			-- 'to next' value for this check to take into account the distance run as calculated
			UPDATE v 
                           SET v.icheckvalues_life_left = v.icheckvalues_life_new - coalesce(distancerun ,0)
                              ,v.icheckvalues_test_left = v.icheckvalues_test_new -  coalesce(distancerun ,0)
                          from icheckvalues as v
                         inner join #temp as t
                          
			   on v.icomponent_id = t.icomponent_ID AND v.ichecktype_id = t.ichecknames_ID			
		
	
	-- Now update the chassis history record to reflect the new distance
	UPDATE ichassishistory 
           SET ichassishistory_distance = @newDistance 
         WHERE ichassishistory_id = @chassisHistoryID

END

Open in new window

0
 

Author Comment

by:ChrisMDrew
ID: 35156339
Thanks for this - I can't pretend to understand all of it but on initial analysis I can sort of see how it works.  One problem I have found is with the line

(SELECT MAX(ichassishistory.ichassishistory_date)

in the second WHERE clause of the new query.  Initially SQL Server complained about this saying that

The multi-part identifier "ichassishistory.ichassishistory_date" could not be bound.

I changed the line to be

(SELECT MAX(ch.ichassishistory_date)

thinking I should refer to the column by its 'short' name but this also fails when I try to install the procedure with

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Unfortunately I don't 100% follow how this query works so am struggling to determine what the error is...

0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35156644
You can't filter on aggregates, like MAX and COUNT, in the WHERE clause of a query - you have to put them in a HAVING clause. I'm not sure how you'd modify this query, since it looks like it should work, but in general you'd modify something like this:

WHERE MAX(SomeColumn) > 10
  AND SomeOtherCriteria...

Open in new window


to this:

WHERE SomeOtherCriteria...
HAVING MAX(SomeColumn) > 10

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35157676
well yes i didn't change the alias in thatt place but the main mistake was forgetting the group by clause...


CREATE PROCEDURE [dbo].[usp_chassishistory_updatedistance]
	@chassisHistoryID int,
	@newDistance real
AS

BEGIN
	
	
	-- Update the components run in the session indicated by the chassis history record to reflect the new distance 
	-- covered in their race and test distances
	UPDATE COMP
           SET COMP.icomponent_life_left = (COMP.icomponent_life_left 
                                         + CHASHIST.ichassishistory_distance - @newDistance)
              ,COMP.icomponent_life_test_left = (COMP.icomponent_life_test_left
                                              + CHASHIST.ichassishistory_distance - @newDistance)
              ,COMP.icomponent_life_weighted = (COMP.icomponent_life_weighted 
	          			     + (CHASHIST.ichassishistory_distance
                                             * COMPHIST.icomponenthistory_factor_value)                        						                     - (@newDistance * COMPHIST.icomponenthistory_factor_value))
	  FROM icomponent COMP
	  Inner JOIN (select * 
                        from icomponenthistory
                       where ichassishistory_id = @chassisHistoryID
                     ) COMPHIST
            ON COMPHIST.icomponent_id = COMP.icomponent_id
	  LEFT outer JOIN ichassishistory CHASHIST 
            ON COMPHIST.ichassishistory_id = CHASHIST.ichassishistory_id
	
	
	
			-- Now we update the checks for this specific component
			SELECT n.ichecknames_id,co.icomponent_id
                              ,SUM(ch.ichassishistory_distance) As distancerun
                          into #temp
                          FROM ichassishistory as ch
			 INNER JOIN icomponenthistory as co
                            ON ch.ichassishistory_id = co.ichassishistory_id
                          cross join  ichecknames as n

			WHERE ch.ichassishistory_id=@chassishistoryid
			  AND ch.ichassishistory_sessionname <> ''
			  AND ch.ichassishistory_date >= 
				(SELECT MAX(xch.ichassishistory_date) 
                                   FROM ichassishistory as xch
				  INNER JOIN icomponenthistory as xco
                                     ON xch.ichassishistory_id = xco.ichassishistory_id
				  WHERE xco.icomponent_id = co.icomponent_ID
				    AND xch.ichassishistory_description LIKE n,ichecknames_name + ' Reset%')
                       group by n.ichecknames_id,co.icomponent_id  --<---- group by


			-- Now that we have the distance run since the last reset of the specified check we need to actually                         -- set the 
			-- 'to next' value for this check to take into account the distance run as calculated
			UPDATE v 
                           SET v.icheckvalues_life_left = v.icheckvalues_life_new - coalesce(distancerun ,0)
                              ,v.icheckvalues_test_left = v.icheckvalues_test_new -  coalesce(distancerun ,0)
                          from icheckvalues as v
                         inner join #temp as t
                          
			   on v.icomponent_id = t.icomponent_ID AND v.ichecktype_id = t.ichecknames_ID			
		
	
	-- Now update the chassis history record to reflect the new distance
	UPDATE ichassishistory 
           SET ichassishistory_distance = @newDistance 
         WHERE ichassishistory_id = @chassisHistoryID

END

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35157688
,,, alias should be xch. in the max(xch....) subquery...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35157701
i also get the impression that you where repeating the joined tables
unnecessarily in the sub queries ... but you haven't explained the actual data relationships or provided any sample data to test with..

(not that i have a test system at present ;-( main laptop bust at present)
0
 

Author Closing Comment

by:ChrisMDrew
ID: 35279724
Thanks for this
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now