Using a Table Valued Function inside a Recursive CTE

Question

I am trying to use a recursive CTE to combine multiple calls to a table valued function.

I have a fiddle at: https://dbfiddle.uk/kktqHivz

I have a table called sales:

create table sales (
    date date,
    total decimal(6,2)
);

The function ds(date) selects all the sales for a date:

create function ds(@date date) returns table as
return select date, total from sales where date=@date;

I thought I could put them into a recursive CTE as follows:

with cte as (
    select cast('2023-01-01' as date) as n, *
    from ds('2023-01-01')
    union all
    select dateadd(day,1,n),d.*
    from cte, ds(n) as d    --  can’t use ds(n) or ds(cte.n)
    where n<'2023-01-05'
)
select * from cte order by n option(maxrecursion 100);

but the message I get is:

Invalid column name 'n'

I’m stuck at this point. The n is a column from the CTE, and normally you can us it in the recursive member, as you see with the SELECT clause. Somehow I can’t use it as an argument for the ds() TVF.

Is there a way I can use the generated date n in the table valued function?

I am confused as to why you would need a recursive CTE for this (unless this example is just much, much less complicated than what you're actually trying to achieve here). Why not just `select [date], total from sales where [date] >= '20230101' and date < '20230105';`? — ZLK, Apr 03 '23 at 04:41
@ZLK This is a trivial example, I know, but I wanted to see whether it’s possible to incorporate a TVF in a CTE. — Manngo, Apr 03 '23 at 05:39

score 1 · Accepted Answer · answered Apr 03 '23 at 11:38

1

As mentioned by the other answer, you would need cross apply.

But the logic of your CTE is incorrect. You need to generate the dates first, then apply the function

with cte as (
    select cast('2023-01-01' as date) as n

    union all

    select dateadd(day, 1, n)
    from cte
    where n < '2023-01-05'
)
select *
from cte
cross apply ds(cte.n)
order by n
option(maxrecursion 100);

Having said that, you should probably just use a straight range lookup

select
  date,
  total
from sales
where date >= @date
  and date < '2023-01-05';

answered Apr 03 '23 at 11:38

Charlieface

52,284
6
19
43

This looks right: I have just tried it on the fiddle and it gives me there results I was looking for. I first thought of doing something similar, but I didn’t use a `cross apply` which solved the problem. Thanks. – Manngo Apr 03 '23 at 22:56
Apropos the simpler option, that’s not available in the real data which is too heavily wrapped in everything else including TVFs. I just needed to build the trivial sample to test the method. – Manngo Apr 03 '23 at 22:57
BTW this is also a helpful example of `CROSS APPLY`. – Manngo Apr 03 '23 at 23:27

score 0 · Answer 2 · edited Apr 03 '23 at 11:35

0

This from cte, ds(n) as d is an old style comma join, so of course ds cannot reference a column in the cte. Instead try a cross apply which is the equivalent of a join for functions that require values from the base table.

with cte as (
    select cast('2023-01-01' as date) as n, *
    from ds('2023-01-01')
    union all
    select dateadd(day,1,n), d.*
    from cte
    cross apply ds(n) as d
    where n < '2023-01-05'
)
select *
from cte
order by n
option(maxrecursion 100);

edited Apr 03 '23 at 11:35

Charlieface

52,284
6
19
43

answered Apr 03 '23 at 03:55

Dale K

25,246
15
42
71

That’s giving me too many rows. I think it’s cross joining all of the data. – Manngo Apr 03 '23 at 04:00
Yip, thats how cross apply works - what do you want it to do? Your original query attempts to do the same thing as you have no join condition either. – Dale K Apr 03 '23 at 04:01
@DaleK, that's because his function returns more than one row, so cte becomes multi-rowed and multiplies itself further :D – siggemannen Apr 03 '23 at 10:06
2

@Manngo, i think it's better if you just generate a list of dates in a CTE, and then join them in one call against ds function – siggemannen Apr 03 '23 at 10:08

Using a Table Valued Function inside a Recursive CTE

2 Answers2