MS SQL в условие отбора IN можно вставлять рекурсию?

#sql #sql_server

вот собственно пример  

     select sum(summa) from (select (dbo.ToTrAmount(trm.Amount,
                                     (select CrncyId  from trbills(nolock) where
id = trm.TrBillId ),
                                     (select DATE  from trbills(nolock) where id
= trm.TrBillId )))as summa 
      FroM bills b(nolock),TrBills trb(nolock),TrMovs trm(nolock),KEKR k(nolock)
          where b.DebetCode = @code 
            and b.TrBillId = trb.id
            and k.Id =trm.KekrId
-------------------------------------------------------------------
            and k.Code in (with rec(childid,parentid,kekrid)
                                as (
                            select  childid,parentid,kekrid from KekrSum where parentid = 1
                            union all
                            select KekrSum.childid,KekrSum.parentid,KekrSum.kekrid
from  KekrSum,rec
                            where 
                            KekrSum.parentid = rec.childid )
                        select distinct * from (
                        select (select code from kekr where id = childid) c from rec 
                        union all
                        select (select code from kekr where id = kekrid) c from rec
                        )a 
                        where (select sumno from kekr where code = a.c)<>1
                            )
-----------------------------------------------
            and trb.Id = trm.TrBillId
            and trb.Date between @dt1 and @dt2)a )

    

Ответы

Ответ 1

Да, можно.
В вашем случае это будет так:

with rec(childid,parentid,kekrid) as (
     select  childid,parentid,kekrid from KekrSum where parentid = 1
     union all
     select KekrSum.childid,KekrSum.parentid,KekrSum.kekrid 
     from  KekrSum,rec
     where KekrSum.parentid = rec.childid 
)

     select sum(summa) from (select (dbo.ToTrAmount(trm.Amount,
                                     (select CrncyId  from trbills(nolock) where
id = trm.TrBillId ),
                                     (select DATE  from trbills(nolock) where id
= trm.TrBillId )))as summa 
      FroM bills b(nolock),TrBills trb(nolock),TrMovs trm(nolock),KEKR k(nolock)
          where b.DebetCode = @code 
            and b.TrBillId = trb.id
            and k.Id =trm.KekrId
-------------------------------------------------------------------
            and k.Code in (
                        select distinct * from (
                            select (select code from kekr where id = childid) c from rec 
                            union all
                            select (select code from kekr where id = kekrid) c from rec
                        )a 
                        where (select sumno from kekr where code = a.c)<>1
                    )
-----------------------------------------------
            and trb.Id = trm.TrBillId
            and trb.Date between @dt1 and @dt2)a )