I've ran into a problem with a view created within a project. The view shows all months (periods) for the total duration of a contract… It turns out we have active contracts that run more than 200 months, so while creating the view using one CTE (periods, see code below), opening the view in the GUI it crashes. This is due to a limitation within SQLServer; a maximum of 100 rows are allowed when using a CTE.
SQLServer provides a workaround, but the client (the GUI in this case) must add something the select instruction when querying the view: MAXRECURSION
Example:
SELECT * FROM service_contract_premium_view OPTION (MAXRECURSION 200)
That would work, but Thinwise has to make an addition to the view definition within the SF.
This would help:

Now, I'm forced to create a piece of crappy code like below. That'll keep working until we have even longer lasting contracts…..:
Code example:
with periodes
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id
, service_contract_id
--, isnull( DateAdd(day , 1 , EOMonth(ending_date) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( DateAdd(day , 1 , EOMonth( isnull( ending_date , case when scheduled_ending_date is null then scheduled_ending_date when scheduled_ending_date > convert(date , getdate()) then scheduled_ending_date else convert(date , getdate()) end ) ) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( starting_date , convert( date , getdate() ) )
, contract_duration
, 1
from service_contract
union all
select
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode + 3
from periodes
where periode < DateDiff( month, starting_date , ending_date )
+ case when day(starting_date) = 1 then 0 else 1 end --Extra periode indien niet beginnend op 1e vd maand
)
, periodes2
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id
, service_contract_id
--, isnull( DateAdd(day , 1 , EOMonth(ending_date) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( DateAdd(day , 1 , EOMonth( isnull( ending_date , case when scheduled_ending_date is null then scheduled_ending_date when scheduled_ending_date > convert(date , getdate()) then scheduled_ending_date else convert(date , getdate()) end ) ) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( starting_date , convert( date , getdate() ) )
, contract_duration
, 2
from service_contract
union all
select
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode + 3
from periodes2
where periode < DateDiff( month, starting_date , ending_date )
+ case when day(starting_date) = 1 then 0 else 1 end --Extra periode indien niet beginnend op 1e vd maand
)
, periodes3
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id
etc etc etc