While Loop/Recursive Statements

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

SQL Server 2012 Forums

Transact-SQL (2012)

While Loop/Recursive Statements

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-23 : 11:21:12

Hi

I have a table, PR_LINK_INV_HST, and I need to find all the records on PR_LINK_INV_HST where the “Client (CSN)” = PR_LINK_INV_HST.CLIENT_ID or PR_LINK_INV_HST.LINKED_CLIENT_ID. and return all LINK_CLIENT_ID’s and CLIENT_ID’s for the specified “Client (CSN)”. then continue looping through the PR_LINK_INV_HST table to also find the clients that are linked to the linked clients of the specified “Client (CSN)”.

so this is my table PR_LINK_INV_HST(CLIENT_ID, LINK_CLIENT_ID)

Can anyone help help me on how to get this right because the query I have gives me an infinite loop.
My Query:

Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Diana (1-14)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Mary (1-33)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana','not linked')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Smith (1-16)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Pope (1-17)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith','not linked')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','linked to Thabo (1-19)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo','not linked')

DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1-23'

;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(

SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0
FROM #PR_LINK_INV_HST
WHERE CLIENT_ID = @CLIENT_ID

UNION ALL

SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1
FROM #PR_LINK_INV_HST HST
JOIN pr_linked LNK
ON HST.CLIENT_ID = LNK.CLIENT_ID

)
SELECT *
INTO #RESULTS
FROM pr_linked
select * from #RESULTS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-23 : 12:16:02

The infinate loop is becuase you are matching ClientID to ClientID.. So you really want to join the ClientID to a portion of the LinkID or vise versa?

Can you show your expected results or explain with data. For example, given teh results of the anchor query:

CLIENT_ID       NAME            LINK_CLIENT_ID                 LEVEL
--------------- --------------- ------------------------------ -----------
1-23            John            linked to Diana (1-14)         0
1-23            John            linked to Mary (1-33)          0

What do you want for the next level?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-23 : 12:19:08

Just a guess, if this isn't right, plese see my post above:

;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(

	SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0
	FROM #PR_LINK_INV_HST 
	WHERE CLIENT_ID = @CLIENT_ID

	UNION ALL

	SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1
	FROM #PR_LINK_INV_HST HST 
	JOIN pr_linked LNK
	ON HST.CLIENT_ID = LEFT(RIGHT(LNK.LINK_CLIENT_ID, 5), 4)

)

select * from pr_linked

PS: Any chance you can fix you data structure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources