CODEWITHSUNDEEP
sqlrecursion
EagerToLearn
EagerToLearn

Reputation: 675

Recursive member of a common table expression 'cte' has multiple recursive references

I have this data

CODE    DATEAPPLY   PARENT_CODE REMARK
A       2022/3/10               A１
A       2020/3/10       X       A2
B       2022/3/10       A       B1
B       2020/3/10               B2
C       2022/3/10       A       C1
C       2020/3/10       A       C2
D       2022/3/10       C       D1
D       2020/3/10       B       D2
E       2022/3/10       D       E1
E       2020/3/10       C       E2

What I want to do is to get all the code and it's child. For example , if I search with the condition = 'C' the query will have to return C and C's child recursively.

But the problem is, there is a DATEAPPLY which will decide what data is valid at the time of the search.

For example, with CODE=C and current date = 20211231 the result should be

CODE    DATEAPPLY   PARENT_CODE REMARK
C       2020/3/10       A       C2     -- this record is get because it's DateApply is closest to the current date
E       2020/3/10       C       E2     -- this record is get because it's DateApply is closet to above record's dateapply and it's parent_code=`C`

But with CODE=C and current date = 20221231 the result should be

CODE    DATEAPPLY   PARENT_CODE REMARK
C       2022/3/10       A       C1
D       2022/3/10       C       D1

Using CTE, I was able to get the code recursively, but with the ApplyDate involved, I can not exclude the unnecessary codes

declare @code varchar(8), @currentdate varchar(8);
set @code = 'C';

set @currentdate = '20221231';

Create table #CODEMASTER(CODE varchar(8), APPLYDATE varchar(8), PARENT_CODE varchar(8), DEL_FLG bit);

insert into #CODEMASTER values('A', '20220310',  '', 0);
insert into #CODEMASTER values('A', '20200310',  'X', 0);
insert into #CODEMASTER values('B', '20220310',  'A', 0);
insert into #CODEMASTER values('B', '20200310',  '', 0);
insert into #CODEMASTER values('C', '20220310',  'A', 0);
insert into #CODEMASTER values('C', '20200310',  'A', 0);
insert into #CODEMASTER values('D', '20220310',  'C', 0);
insert into #CODEMASTER values('D', '20200310',  'B', 0);
insert into #CODEMASTER values('E', '20220310',  'D', 0);
insert into #CODEMASTER values('E', '20200310',  'C', 0);

;with cte as 
(
    select *, cast(0 as bigint) as seqnum   from #CODEMASTER where CODE=@CODE and APPLYDATE = (select max(APPLYDATE) from #CODEMASTER where APPLYDATE < @currentdate and CODE=@CODE)
    union all
    Select * from (
        select t.*, row_number() over (partition by t.CODE, t.APPLYDATE order by t.APPLYDATE desc) as seqnum
        from   #CODEMASTER t
        inner join cte on cte.CODE = t.PARENT_CODE AND t.APPLYDATE <= cte.APPLYDATE
        and cte.CODE <> t.CODE
      --  where t.CODE not in (select CODE from cte)
        ) as q
        where q.seqnum = 1 
)

select * from cte;

The commented part is where I need to include to exclude the already existed code in the cte result

      --  where t.CODE not in (select CODE from cte)

I'm getting this error :

Recursive member of a common table expression 'cte' has multiple recursive references.

Is there any work around to achieve this using pure query without using loop ? Thanks.

Upvotes: 0

Views: 83

Answers (1)

RegBes
RegBes

Reputation: 559

Hi EagerToLearn try this

declare @code varchar(8), @currentdate varchar(8);
set @code = 'C';

set @currentdate = '20221231';

Create table #CODEMASTER(CODE varchar(8), APPLYDATE varchar(8), PARENT_CODE varchar(8), DEL_FLG bit);

insert into #CODEMASTER values('A', '20220310',  '', 0);
insert into #CODEMASTER values('A', '20200310',  'X', 0);
insert into #CODEMASTER values('B', '20220310',  'A', 0);
insert into #CODEMASTER values('B', '20200310',  '', 0);
insert into #CODEMASTER values('C', '20220310',  'A', 0);
insert into #CODEMASTER values('C', '20200310',  'A', 0);
insert into #CODEMASTER values('D', '20220310',  'C', 0);
insert into #CODEMASTER values('D', '20200310',  'B', 0);
insert into #CODEMASTER values('E', '20220310',  'D', 0);
insert into #CODEMASTER values('E', '20200310',  'C', 0);

;with 
RecentCodemaster as (
select #codemaster.*
from
#codemaster
inner join
(select 
CODE
,min(abs(datediff(dd,datefromparts(left( applydate,4),substring(applydate,5,2),right(applydate,2))
,datefromparts(left( @currentdate,4),substring(@currentdate,5,2),right(@currentdate,2))))) MinDayApart
from 
#codemaster
group by CODE) as CloseApply
on #codemaster.code = closeapply.code
and abs(datediff(dd,datefromparts(left( applydate,4),substring(applydate,5,2),right(applydate,2))
,datefromparts(left( @currentdate,4),substring(@currentdate,5,2),right(@currentdate,2)))) = CloseApply.MinDayApart)

,cte as 
(
    select 
    Code
    ,Applydate
    ,parent_code
     from RecentCodemaster where  CODE=@CODE
    union all
    Select 
    #codemaster.Code
    ,#codemaster.Applydate
    ,#codemaster.parent_code
     from 
    #codemaster
    inner join
    cte 
    on cte.Code = #codemaster.parent_code
    and cte.applydate = #codemaster.applydate

)

select * from cte;

the trick here is to set up the RecentCodemaster CTE that finds all the recs that are close to your current date then do the recursion with that as the parent data set filtered by @code.

Upvotes: 0

Related Questions