[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

TSQL cursor algorithm to populate null values

I'm trying to do some data cleanup and the need to use cursors and conditional logic has arisen.

I have a "Company" table with some null values for a "Section" field. I want to be able to look at other tables and see if I can find the same CompanyID so I can then populate the null "Section" based on where I found the CompanyID in the other table. For example if the CompanyID is found in the Retail table the value for "Section" in the Company table will be "RET" for that record.

I"m working with 5 tables.  MY_Company, Retail_Info, Office_Info, Industrial_Info, MY_UnmachedRecords

The idea is to get a list of CompanyID's with null "Sections". Use that list of CompanyID's to look in Retail_Info, Office_Info and Industrial_Info to see if the CompanyID is found in there. If so, update the MY_Company.Section with whatever section the CompanyID is found, either "RET", "OF" or "IND".  If I have looked through the Retail_Info, Office_Info, and Industrial_Info tables and there is still no match, I want to insert that CompanyID into the "MY_UnmachedRecords" table.

Table MY_Company has two relavent fields:  CompanyID and Section.
CompanyID represents the unique identifier for that particular company.
Section represents what type of industry it is. The only 3 possible values are "RET", "OFF", "IND" to represent Retail, Office and Industrial sections.

Table Retail_Info has 3 relavent fields: LeaseCo, ManagementCo, OwnersCo
Table Office_Info has 3 relavent fields: LeaseCo, ManagementCo, OwnersCo
Table Industrial_Info has 3 relavent fields: LeaseCo, ManagementCo, OwnersCo

The LeaseCo, ManagementCo, and OwnersCo fields are all CompanyID numbers.

I have the basic outer skeleton working but I can't figure out how to do the acutal
processing. Please take a look at the script below and let me know if you have any
suggestions on how I can create the parts that are commented in
/*    ...     */

------------------------------------------------------------------
DECLARE @CompID INT
DECLARE @getCompID CURSOR
   
SET @getCompID = CURSOR FOR
select CompID from OCN_Company where Section is null

OPEN @getCompID
    FETCH NEXT
    FROM @getCompID INTO @CompID
WHILE @@FETCH_STATUS = 0
BEGIN
     -- // start work
      DECLARE @isFound bit
        SET @isFound = 0
      
           /* look in Retail_Info.LeaseCo for company match.
            if matched UPDATE MY_Comapny with "RET"
            if NOT mached look in Retail_Info.ManagementCo.
            if matched UPDATE MY_Company with "RET".
            if NOT mached look in Retail_Info.OwnersCo
            if matched UPDATE My_Company with "RET"
            if NOT mached look in Office_Info.LeaseCo

          if matched UPDATE My_Company with "OF".
            if NOT mached look in Office_Info.LeaseCo.
            if matched UPDATE MY_Company with "OF". Move to next CompanyID
            if NOT mached look in Office_Info.ManagementCo.
            if mached UPDATE MY_Company with "OF".
            if NOT mached look in Office_Info.OwnersCo
            if mached UPDATE MY_Company with "OF"
            if NOT mached look in INDUSTRIAL_Info.LeaseCo

            if mached UPDATE MY_Company with "IND".
            if NOT mached look in Industrial_Info.LeaseCo
            if matched UPDATE MY_Company with "IND"
            if NOT mached look in Industrial_Info.ManagementCo.
            if matched UPDATE MY_Company with "IND"
            if NOT mached look in Industrial_Info.OwnersCo
            if mached UPDATE MY_Company with "IND"

            if NOT mached UPDATE MY_UnmachedRecords table with the companyID and
            move to next CompanyID.
            */
             

     -- // end work

     FETCH NEXT
     FROM @getCompID INTO @CompID
END
 CLOSE @getCompID
DEALLOCATE @getCompID

------------------------------------------------------------------

I'm using SQL Server 2000.

 Any help is appreciated.


0
FastEddie___
Asked:
FastEddie___
2 Solutions
 
DimitrisSenior Solution ArchitectCommented:
Here u r
Works just fine
DECLARE @CompID INT
DECLARE @isFound bit
DECLARE @tmpCMP_TYPE -- Stores the type of the CMP RET-OF-IND
 
DECLARE rst CURSOR LOCAL FAST_FORWARD FOR
select	CompID	
from	OCN_Company 
where	Section is null
 
OPEN ret
FETCH NEXT rst FROM INTO @CompID
WHILE @@FETCH_STATUS = 0
BEGIN
     -- // start work
        SET @isFound = 0
		SET @tmpCMP_TYPE = NULL
		SET @tmpCMP_TYPE =	(SELECT	'RET' 
							FROM	Retail_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
 
		SET @tmpCMP_TYPE =	(SELECT	'OF' 
							FROM	Office_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
 
		SET @tmpCMP_TYPE =	(SELECT	'IND' 
							FROM	Industrial_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
		-- IF the cursor is here this means that no mutch was found
		-- so insert a record to MY_UnmachedRecords
		INSERT INTO MY_UnmachedRecords
		VALUES (@CompID)	
		
FetchNextRecord:-- This works as a label
				-- IF the code finds a match it will do the update and then it will jump to this point 
				-- in order to fetch next record
	FETCH NEXT FROM rst INTO @CompID
END
CLOSE rst
DEALLOCATE rst

Open in new window

0
 
DimitrisSenior Solution ArchitectCommented:
oups :)
I had some minor bugs
So use this :P


DECLARE @CompID INT
DECLARE @isFound bit
DECLARE @tmpCMP_TYPE nvarchar -- Stores the type of the CMP RET-OF-IND
 
DECLARE rst CURSOR LOCAL FAST_FORWARD FOR
select	CompID	
from	OCN_Company 
where	Section is null
 
OPEN rst
FETCH NEXT FROM rst  INTO @CompID
WHILE @@FETCH_STATUS = 0
BEGIN
     -- // start work
        SET @isFound = 0
		SET @tmpCMP_TYPE = NULL
		SET @tmpCMP_TYPE =	(SELECT	'RET' 
							FROM	Retail_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
 
		SET @tmpCMP_TYPE =	(SELECT	'OF' 
							FROM	Office_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
 
		SET @tmpCMP_TYPE =	(SELECT	'IND' 
							FROM	Industrial_Info
							WHERE	LeaseCo = @CompID
									OR ManagementCo=@CompID
									OR OwnersCo = @CompID)
		IF NOT @tmpCMP_TYPE IS NULL
		BEGIN
			UPDATE	OCN_Company
			SET		Section= @tmpCMP_TYPE
			WHERE	CompID = @CompID
			GoTo FetchNextRecord
		END
		-- IF the cursor is here this means that no mutch was found
		-- so insert a record to MY_UnmachedRecords
		INSERT INTO MY_UnmachedRecords
		VALUES (@CompID)	
		
FetchNextRecord:-- This works as a label
				-- IF the code finds a match it will do the update and then it will jump to this point 
				-- in order to fetch next record
	FETCH NEXT FROM rst INTO @CompID
END
CLOSE rst
DEALLOCATE rst

Open in new window

0
 
appariCommented:
you can do it without using cursors as follows,


Update OCN_Company
Set Section = 'RET'
From OCN_Company Join Retail_Info
ON OCN_Company.CompID = Retail_Info.LeaseCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'RET'
From OCN_Company Join Retail_Info
ON OCN_Company.CompID = Retail_Info.ManagementCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'RET'
From OCN_Company Join Retail_Info
ON OCN_Company.CompID = Retail_Info.OwnersCo
where OCN_Company.Section is null
 
 
Update OCN_Company
Set Section = 'OF'
From OCN_Company Join Office_Info
ON OCN_Company.CompID = Office_Info.LeaseCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'OF'
From OCN_Company Join Office_Info
ON OCN_Company.CompID = Office_Info.ManagementCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'OF'
From OCN_Company Join Office_Info
ON OCN_Company.CompID = Office_Info.OwnersCo
where OCN_Company.Section is null
 
 
 
Update OCN_Company
Set Section = 'IND'
From OCN_Company Join INDUSTRIAL_Info
ON OCN_Company.CompID = INDUSTRIAL_Info.LeaseCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'IND'
From OCN_Company Join INDUSTRIAL_Info
ON OCN_Company.CompID = INDUSTRIAL_Info.ManagementCo
where OCN_Company.Section is null
 
Update OCN_Company
Set Section = 'IND'
From OCN_Company Join INDUSTRIAL_Info
ON OCN_Company.CompID = INDUSTRIAL_Info.OwnersCo
where OCN_Company.Section is null
 
 
Insert Into MY_UnmachedRecords(companyID)
select CompID from OCN_Company where Section is null

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
Why do you need to use a CURSOR when a simple UPDATE would do?
0
 
FastEddie___Author Commented:
Thanks so much for your responses.

I guess the cursor isn't absolutely necessary since it can be done using only update statements however. this situation is sure to come up in the future and I wanted to have a template in place where the processing could be done all at once. That way I could just plug in different tables on future projects. This type of lookup seems quite common so it's useful to to have a module that can be resued.

The algorithm gets a list of some kind and starts performing a bunch of comparisons on different tables then updates another table based on what it finds or where it find it. It just seems that this would be a good thing to utilize as a module. There are some concerns with cursers being slow to run but since this operation only for data cleanup I'm not particularly worried if it is a little slow to run.

Both solutions are accurate and correct so I'm splitting points.

Thank you very much for your expert solutions.

-Eddie


0
 
FastEddie___Author Commented:
Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now