CTE Recursive

  • Hi

    I have a query below and I will explain what I wish to archive. I have two methods that I need to do, (1)find all the records on #PR_LINK_INV_HST where the @Client_Id = #PR_LINK_INV_HST.CLIENT_ID or #PR_LINK_INV_HST.LINKED_CLIENT_ID (2) find the clients that are linked to the linked clients of the specified Client, we call this “deep linking”.

    All the clients will be on CLIENT_ID, and the clients they are linked to will be on LINKED_CLIENT_ID. Now e.g, if our specified client is Client A we will find client A(CLIENT_ID) is linked to client B(LINKED_CLIENT_ID), if client B(LINKED_CLIENT_ID) is also a child to client C(CLIENT_ID), we must also show this row because client C is indirectly linked to A because of client B.

    below is my query, whih somehow my logic is wrong, because when I select LINK_CLIENT_ID only on my finaly DISTINCT SELECT, I get 6 rows back, which is correct, but when I select all three colums I get 14 back, which is wrong, I have to get only 6 rows.

    Please help.

    --create PR_LINK_INV_HST temp table--

    Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))

    --insert into PR_LINK_INV_HST temp table--

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')

    insert into #PR_LINK_INV_HST

    Select LINK_CLIENT_ID,CLIENT_ID,LINK_REASON

    FROM #PR_LINK_INV_HST

    declare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9'

    ;WITH cte AS

    (

    SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON, CAST(CLIENT_ID + '/' AS VARCHAR(MAX)) AS traversed

    FROM #PR_LINK_INV_HST

    WHERE @CLIENT_ID = CLIENT_ID

    UNION ALL

    SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON, CAST(traversed+t.client_id + '/' AS VARCHAR(MAX)) AS traversed

    FROM #PR_LINK_INV_HST t

    INNER JOIN cte c ON c.LINK_CLIENT_ID = t.CLIENT_ID

    WHERE traversed NOT LIKE '%'+t.client_id + '%'

    )

    SELECT DISTINCT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON FROM cte

    drop table #PR_LINK_INV_HST

  • Your data set is causing heaps of problems here - there are associations where there shouldn't be. I think the query is ok, in fact with properly formed data, you probably won't need the WHERE clause in the recursive part:

    --create PR_LINK_INV_HST temp table--

    DROP TABLE #PR_LINK_INV_HST

    Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))

    --insert into PR_LINK_INV_HST temp table--

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')

    --Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')

    Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')

    insert into #PR_LINK_INV_HST

    Select LINK_CLIENT_ID,CLIENT_ID,LINK_REASON

    FROM #PR_LINK_INV_HST

    declare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9'

    ;WITH cte AS

    (

    SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON,

    [Level] = 1,

    traversed = CAST(CLIENT_ID AS VARCHAR(8000))

    FROM #PR_LINK_INV_HST

    WHERE CLIENT_ID = @CLIENT_ID

    UNION ALL

    SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON,

    [Level] = c.[Level] + 1,

    traversed = CAST(c.traversed + ' / ' + t.client_id AS VARCHAR(8000))

    FROM #PR_LINK_INV_HST t

    INNER JOIN cte c

    ON c.LINK_CLIENT_ID = t.CLIENT_ID

    -- probably not required with well-formed data

    WHERE c.traversed NOT LIKE '%'+t.client_id + '%'

    )

    SELECT *

    FROM cte

    ORDER BY [Level], CLIENT_ID

    OPTION (MAXRECURSION 0)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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