SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CASE to replace while loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robowski
Yak Posting Veteran

99 Posts

Posted - 03/08/2013 :  07:30:42  Show Profile  Reply with Quote
As a temp fix until the software can be changed I have created a sproc to repair a relationship table that is being built incorrectly.

I can run it as a while loop, but because of the tens of thousands of rows it takes a long time. I was wondering if the below could be done by a case statement to improve performance?

basically, it's a parent/child/grandchild/great .. etc

There three key fields are person.NameID(varchar) person.ParentID(varchar), Person.Gen(int) - (their generation)

Then there is the relationship table PrsonsRel
fields PrsonsRel.childID & PrsonsRel.Rel(relation)

there should be entries for all persons in the childID and in PersonsRel it should show the father, another entry along below for grandfather if they have one, another below great grand etc or a --- if they are a generation 1 with a NULL for person.ParentId


I.E a third generation (has father and grand father)
PrsonsRel.childID PrsonsRel.Rel
Brian -- /*(all have an entry like this*/)
Brian John234 /*(Father)*/
Brian Dave5674 /*(GrandFather)*/

Because the table is designed to only link the child to his immediate parent, I can only rebuild the relation table by looping through from youngest to oldest.

Any ideas if/how this could be put into a case statement? or is a loop the only way to go?

As mentioned, this is only as a temp fix so any table changes would not be worth it.


Cheers

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/08/2013 :  07:44:38  Show Profile  Reply with Quote
You can join the table onto itself to get the parent and grandparent (and then use that to update the table if necessary). Code below is psuedocode - if you post DDL and sample data, it can be made more specific:
;WITH cte AS
(
	SELECT
		c.*,
		p.name,
		gp.name
	FROM
		YourTable c
		LEFT JOIN YourTable p -- for parent
			ON p.nameId = c.ParentId
		LEFT JOIN YourTable gp -- for grandparent
			ON gp.nameId = p.ParentId
)
SELECT * FROM cte;
-- or your update statement here using the cte


If you need help in posting DDL and sample data in a consumable format, these links might help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Robowski
Yak Posting Veteran

99 Posts

Posted - 03/08/2013 :  09:18:25  Show Profile  Reply with Quote
So that's the benefit of having Cte's as being self referencing!

Thanks James, I'll have a go from here out (learn by doing!) and see how it goes.

Much appreciated.

Rob

quote:
Originally posted by James K

You can join the table onto itself to get the parent and grandparent (and then use that to update the table if necessary). Code below is psuedocode - if you post DDL and sample data, it can be made more specific:
;WITH cte AS
(
	SELECT
		c.*,
		p.name,
		gp.name
	FROM
		YourTable c
		LEFT JOIN YourTable p -- for parent
			ON p.nameId = c.ParentId
		LEFT JOIN YourTable gp -- for grandparent
			ON gp.nameId = p.ParentId
)
SELECT * FROM cte;
-- or your update statement here using the cte


If you need help in posting DDL and sample data in a consumable format, these links might help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

Robowski
Yak Posting Veteran

99 Posts

Posted - 03/08/2013 :  10:30:09  Show Profile  Reply with Quote
James,

The problem I had when first trying to do this as a non loop update, was that the the generation would vary in regard to how many grand, or great grands there were.Below is some sample code if this makes more sense


IF OBJECT_ID('Persons', 'U') IS NOT NULL
	TRUNCATE TABLE Persons
ELSE
	CREATE TABLE Persons
		(
		ChildID varchar(10) NOT NULL
		,ParentID varchar(10) NULL
		,Gen int NOT NULL
		)
GO

IF OBJECT_ID('Persons', 'U') IS NOT NULL
	TRUNCATE TABLE PrsonsRel
ELSE
	CREATE TABLE PrsonsRel
		(
		ChildID varchar(10) NOT NULL
		,Rel varchar(10) NOT NULL
		)
GO



INSERT INTO Persons
--1st generation Brian
Values ('Brian', NULL, 1)
INSERT INTO Persons
--2nd generation John (1st son)
Values ('John', 'Brian', 2)
INSERT INTO Persons
--2nd generation wayne (2nd son) Wayne has no sons, ends here with him)
Values ('Wayne', 'Brian', 2)
INSERT INTO Persons
--3rd generation Dave - Dave has no sons - ends here.
Values ('Dave', 'John', 3)
INSERT INTO Persons
--1st generation Rees - No sons ends here
Values ('Rees', NULL, 1)
INSERT INTO Persons
--1st generation
Values ('Craig', NULL, 1)
INSERT INTO Persons
--2nd generation Keith 
Values ('Keith', 'Craig', 2)
INSERT INTO Persons
--3rd generation Fred
Values ('Fred', 'Keith', 3)
INSERT INTO Persons
--4th generation Andy
Values ('Andy', 'Fred', 4)
INSERT INTO Persons
--4th generation steve
Values ('Steve', 'Fred', 4)


I trying to build the relationship table to display the below results without using a loop if possible.

SELECT r.*, p.gen FROM PrsonsRel r JOIN Persons p ON r.ChildID = p.ChildID Order by r.childID, p.gen asc


ChildID    Rel        gen
---------- ---------- -----------
Andy       ---        4
Andy       Fred       4
Andy       Keith      4
Andy       Craig      4
Brian      ---        1
Craig      ---        1
Dave       ---        3
Dave       John       3
Dave       Brian      3
Fred       ---        3
Fred       Craig      3
Fred       Keith      3
John       ---        2
John       Brian      2
Keith      ---        2
Keith      Craig      2
Rees       ---        1
Steve      ---        4
Steve      Fred       4
Steve      Keith      4
Steve      Craig      4
Wayne      ---        2
Wayne      Brian      2
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/08/2013 :  10:50:33  Show Profile  Reply with Quote
Can you post the data to populate the PrsonsRel table also?
Go to Top of Page

Robowski
Yak Posting Veteran

99 Posts

Posted - 03/08/2013 :  11:21:21  Show Profile  Reply with Quote
quote:
Originally posted by James K

Can you post the data to populate the PrsonsRel table also?





INSERT INTO PrsonsRel
Values ('Andy', '---')
INSERT INTO PrsonsRel
Values ('Andy', 'Fred')
INSERT INTO PrsonsRel
Values ('Andy', 'Keith')
INSERT INTO PrsonsRel
Values ('Andy', 'Craig')
INSERT INTO PrsonsRel
Values ('Brian', '---')
INSERT INTO PrsonsRel
Values ('Craig', '---')
INSERT INTO PrsonsRel
Values ('Dave', '---')
INSERT INTO PrsonsRel
Values ('Dave', 'John')
INSERT INTO PrsonsRel
Values ('Dave', 'Brian')
INSERT INTO PrsonsRel
Values ('Fred', '---')
INSERT INTO PrsonsRel
Values ('Fred', 'Craig')
INSERT INTO PrsonsRel
Values ('Fred', 'Keith')
INSERT INTO PrsonsRel
Values ('John', '---')
INSERT INTO PrsonsRel
Values ('John', 'Brian')
INSERT INTO PrsonsRel
Values ('Keith', '---')
INSERT INTO PrsonsRel
Values ('Keith', 'Craig')
INSERT INTO PrsonsRel
Values ('Rees', '---')
INSERT INTO PrsonsRel
Values ('Steve', '---')
INSERT INTO PrsonsRel
Values ('Steve', 'Fred')
INSERT INTO PrsonsRel
Values ('Steve', 'Keith')
INSERT INTO PrsonsRel
Values ('Steve', 'Craig')
INSERT INTO PrsonsRel
Values ('Wayne', ' ---')
INSERT INTO PrsonsRel
Values ('Wayne', 'Brian')



Cheers
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/08/2013 :  14:23:36  Show Profile  Reply with Quote
I didn't quite follow the relationship logic, but the following query gives you the output you posted:
SELECT DISTINCT 
	r.*,
	p.Gen
FROM
	PrsonsRel r
	INNER JOIN Persons p ON p.ChildId = r.ChildId
ORDER BY
	ChildId;
Go to Top of Page

Robowski
Yak Posting Veteran

99 Posts

Posted - 03/08/2013 :  16:27:41  Show Profile  Reply with Quote
James,

Thanks for your response but I'm trying to build that table not query it.

So, assume PrsonsRel is empty or has been truncated, or because of the software fault is incorrect and needs updating. I need to then populate it with the correct information from the Persons table.

Every ChilID should have an entry is in PrsonsRel for it's father, and it's fathers father(s)

so if a child was Fourth generation, i.E it has a father, it's father has a father record(it's grand father) and it's fathers father has a father record (it's great grand father)there would be 4 entries for it in PrsonsRel

ChildID Rel
---------- ----------
Andy --- -- all it's have this entry once
Andy Fred -- it's father's, father's father.
Andy Keith -- it's fathers father
Andy Craig --it's father

There is no limit really for how long this can go on, depends on the data. I have created a script that loops through each generation and builds these records based on a MAX(gen) so the script neevr has to be changed. However the performance is very poor and usually a case statement instead of a while loop is better?

I can't figure out how to create this though without constantly amending it if newer generations are added. Does this make sense?


quote:
Originally posted by James K

I didn't quite follow the relationship logic, but the following query gives you the output you posted:
SELECT DISTINCT 
	r.*,
	p.Gen
FROM
	PrsonsRel r
	INNER JOIN Persons p ON p.ChildId = r.ChildId
ORDER BY
	ChildId;


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000