While Loop/ CTE Statement

  • 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

  • Hi

    The problem appears to be that you are joining in CLIENT_ID to CLIENT_ID in your recursive query rather than HST.CLIENT_ID to LNK.LINK_CLIENT_ID.

    I'm assuming that the extra descriptive text in the LINK_CLIENT_ID is not in the real data

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply