Recursive 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

Recursive Loop

Author

Topic

nvsdq
Starting Member

5 Posts

Posted - 2011-09-06 : 10:34:25

I have a hierarchical structure where relations got inherited top down. I now want to select all records which do not have a certain relation and then display from which node the Relation gets inherited.

My table has the columns OBJID, RELATION and NODEID. Here an example:

OBJID REL NODEID
10001057 0 b.01.01
10001068 0 b.01.02.05
10001601 0 b.06.01.10
10001685 0 b.06.15
10002254 0 b.01.01.01.01.03
10003075 0 b.01.01.06.03

What I need now is a kind of recursive loop that reduces the NODEID from the left side by 3 characters and check on this level whether the Relation is unequal 0. If it is 0 the relation must be checked one level up and so forth.
As an example, my final output should somehow look like this:

OBJID REL NODEID REL_INH NODEID_INHERITED
10001057 0 b.01.01 1 b.01
10001068 0 b.01.02.05 1 b.01
10001601 0 b.06.01.10 1 b.06.01.
10001685 0 b.06.15 1 b.06
10002254 0 b.01.01.01.01.03 1 b.01
10003075 0 b.01.01.06.03 1 b.01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:05:51

can you explain how derived nodeid_inherited for b.06.01.10 and b.01.01.01.01.03 ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nvsdq
Starting Member

5 Posts

Posted - 2011-09-06 : 11:20:59

my source table is a pivot table where I have the ObjectID (OBJID), the Relation which is 0 or 1 and the NODEID which allows me to identify the parant node.
So for example when I check the relationship on b.01.01.01.01.03 and it is 0 the next check must be b.01.01.01.01 and when this is 0 for b.01.01.01 and so on.

The goal is to first select all records where the relation is 0 and then go buttom up the hierarchy till a relationship is available.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:44:32

so do you mean you've records with NODEID b.01 and b.06.01?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nvsdq
Starting Member

5 Posts

Posted - 2011-09-07 : 02:12:20

yes, the whole three structure is in the table. E.g.

b
b.01
b.02
b.03
b.03.01
b.03.02
b.03.01.01
b.03.01.02
b.04
b.05
b.06.01
b.06.02
b.06.03

nvsdq
Starting Member

5 Posts

Posted - 2011-09-07 : 05:26:19

Below an example of my code. With this nested select statement I can only go one level up respectively I would need to further nest it. This gets difficult when the structure gets too deep.
See also the picture: for the seleced row it should be checked on node a.12 whether the relation exists.So I think a recursion should be the right solution that overrites the column NODEID_1 till all relations have the value 1.


SELECT 
	LEVEL_1.OBJID,
	LEVEL_1.NODEID,
	LEVEL_1.A011_OCCURRENCE,
	LEVEL_1.NODEID_1,
	LEVEL_2.A011_LEVEL_1
FROM (
SELECT 
	PIVOT_REL_O.OBJID, 
	PIVOT_REL_O.NODEID, 
	PIVOT_REL_O.A011 AS A011_OCCURRENCE, 
	'NODEID_1' = 
	case  
		when len(dbo.PIVOT_REL_O.NODEID)-3 > 0 
		then LEFT(dbo.PIVOT_REL_O.NODEID,LEN(dbo.PIVOT_REL_O.NODEID)-3) 
		else 'not applicable'
		end 
FROM         
	dbo.PIVOT_REL_O
WHERE     
	(PIVOT_REL_O.A011 <> '1') AND (PIVOT_REL_O.LEVELID >= '1')) LEVEL_1 LEFT JOIN
(SELECT
	OBJID,
	NODEID,
	A011 AS 'A011_LEVEL_1'
FROM
	PIVOT_REL_O) AS LEVEL_2 ON
	LEVEL_1.NODEID_1 = LEVEL_2.NODEID
WHERE 
	LEVEL_1.OBJID IS NOT NULL

nvsdq
Starting Member

5 Posts

Posted - 2011-09-07 : 11:29:27

Below some sample data


CREATE TABLE test_data
(OBJID varchar(50),
A011 varchar(50),
NODEID varchar(50))

INSERT INTO test_data VALUES ('10045487','0','a.01')
INSERT INTO test_data VALUES ('10045488','1','a.01.01')
INSERT INTO test_data VALUES ('10045489','1','a.01.01.01')
INSERT INTO test_data VALUES ('10045493','1','a.01.04.03')
INSERT INTO test_data VALUES ('10045494','0','a.01.02.01')
INSERT INTO test_data VALUES ('10045495','1','a.01.02')
INSERT INTO test_data VALUES ('10045496','1','a.01.02.02')
INSERT INTO test_data VALUES ('10045498','1','a.01.02.03')
INSERT INTO test_data VALUES ('10045499','1','a.01.02.04')
INSERT INTO test_data VALUES ('10045501','1','a.01.03')
INSERT INTO test_data VALUES ('10045502','1','a.01.03.01')
INSERT INTO test_data VALUES ('10045503','1','a.01.02.05')
INSERT INTO test_data VALUES ('10045504','0','a.01.03.02')
INSERT INTO test_data VALUES ('10045508','1','a.01.04')
INSERT INTO test_data VALUES ('10045509','1','a.01.04.04')
INSERT INTO test_data VALUES ('10045511','1','a.01.04.05')
INSERT INTO test_data VALUES ('10045519','0','a.01.05')
INSERT INTO test_data VALUES ('10045520','1','a.01.06')
INSERT INTO test_data VALUES ('10045521','1','a.01.07')
INSERT INTO test_data VALUES ('10045807','0','a.01.02.06')
INSERT INTO test_data VALUES ('10045816','1','a.01.04.06')
INSERT INTO test_data VALUES ('10045836','0','a.01.01.02')
INSERT INTO test_data VALUES ('10045837','1','a.01.04.07')
INSERT INTO test_data VALUES ('10045842','1','a.01.08')
INSERT INTO test_data VALUES ('10045843','0','a.01.03.11')
INSERT INTO test_data VALUES ('10045844','1','a.01.04.11')
INSERT INTO test_data VALUES ('10045847','1','a.01.04.12')
INSERT INTO test_data VALUES ('10045486','1','a')

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-07 : 13:05:46

INSERT INTO test_data VALUES ('10045487','0','a.01')
INSERT INTO test_data VALUES ('10045488','1','a.01.01')
INSERT INTO test_data VALUES ('10045489','1','a.01.01.01')
INSERT INTO test_data VALUES ('10045493','1','a.01.04.03')
INSERT INTO test_data VALUES ('10045494','0','a.01.02.01')
INSERT INTO test_data VALUES ('10045495','1','a.01.02')
INSERT INTO test_data VALUES ('10045496','1','a.01.02.02')
INSERT INTO test_data VALUES ('10045498','1','a.01.02.03')
INSERT INTO test_data VALUES ('10045499','1','a.01.02.04')
INSERT INTO test_data VALUES ('10045501','1','a.01.03')
INSERT INTO test_data VALUES ('10045502','1','a.01.03.01')
INSERT INTO test_data VALUES ('10045503','1','a.01.02.05')
INSERT INTO test_data VALUES ('10045504','0','a.01.03.02')
INSERT INTO test_data VALUES ('10045508','1','a.01.04')
INSERT INTO test_data VALUES ('10045509','1','a.01.04.04')
INSERT INTO test_data VALUES ('10045511','1','a.01.04.05')
INSERT INTO test_data VALUES ('10045519','0','a.01.05')
INSERT INTO test_data VALUES ('10045520','1','a.01.06')
INSERT INTO test_data VALUES ('10045521','1','a.01.07')
INSERT INTO test_data VALUES ('10045807','0','a.01.02.06')
INSERT INTO test_data VALUES ('10045816','1','a.01.04.06')
INSERT INTO test_data VALUES ('10045836','0','a.01.01.02')
INSERT INTO test_data VALUES ('10045837','1','a.01.04.07')
INSERT INTO test_data VALUES ('10045842','1','a.01.08')
INSERT INTO test_data VALUES ('10045843','0','a.01.03.11')
INSERT INTO test_data VALUES ('10045844','1','a.01.04.11')
INSERT INTO test_data VALUES ('10045847','1','a.01.04.12')
INSERT INTO test_data VALUES ('10045486','1','a')

What is expected output for that input data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources