here is an example

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 = ac)<>1 ) ----------------------------------------------- and trb.Id = trm.TrBillId and trb.Date between @dt1 and @dt2)a )
  • Off topic: did you think about query optimization? Request so much monstrous size and debug / edit is difficult, and it will be long for a large amount of data. - Regent
  • while the task is to start, before optimization is still far away - Stolborez
  • The CTE (with construction) cannot be used in a subquery, only at the topmost level. So you will have to implement your request in a different way. - ApInvent

1 answer 1

Yes you can. In your case it will be like this:

 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 = ac)<>1 ) ----------------------------------------------- and trb.Id = trm.TrBillId and trb.Date between @dt1 and @dt2)a )