We have been playing with recursive tables lately and we wanted to see if we could make one to create a forward-looking date table. Similar to what is shown in the examples below:
What we wanted to change was to build our recursion off the current month and year so that it would flow as time went on. To do this we built a calculated date field off of the Epicor constants month and year. This placed into a CTE with our company table seems to work somewhat. After building the iterative table we have it working for one of our companies. But, for the others, it only shows the anchor value and then nothing. We have a “Bad SQL statement” somewhere, but when we check within the calculated fields there are no errors. Below is attached the BAQ and SQL view of the BAQ. We would like some help diagnosing what we are missing:
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [Anchor] as
(select distinct
[Company].[Company] as [Company_Company],
(convert(date, convert(varchar, Constants.Month) +'/01/'+ convert(varchar, Constants.Year), 101)) as [Calculated_AMGStartDate],
(0) as [Calculated_Count]
from Erp.Company as Company
group by [Company].[Company]
union all
select
[Company1].[Company] as [Company1_Company],
(DATEADD(month, increment, convert(date, convert(varchar, Constants.Month) +'/01/'+ convert(varchar, Constants.Year), 101))) as [Calculated_DateIncrement],
(Anchor.Calculated_Count + 1) as [Calculated_increment]
from Anchor as Anchor
inner join Erp.Company as Company1 on
Company1.Company = Anchor.Company_Company)
select
[Anchor1].[Company_Company] as [Company_Company],
[Anchor1].[Calculated_AMGStartDate] as [Calculated_AMGStartDate],
[Anchor1].[Calculated_Count] as [Calculated_Count]
from Anchor as Anchor1
Not sure what error you were getting
but I was seeing a maximum recursion error.
So… I added a NumberOfMonths parameter to limit the count (12 in this example = 13 rows)…
TBD if that would apply on your end?
That was it! On my end, it just says Bad SQL statement and to look at the server event logs. Being on the cloud we are not allowed access to those. Limiting my recursions to 12, which equals 12 months, we got all our results the way they should be! Thanks Bruce !!!
