CASE to replace while loop - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CASE to replace while loop

Author

Topic

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 07:30:42

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 07:44:38

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

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 09:18:25

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

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 10:30:09

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 10:50:33

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

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 11:21:21

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 14:23:36

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;

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 16:27:41

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources