Skip to main content

Idea pipeline (top 25)

Filter by idea status

Filter by product

1968 Ideas

Add MAXRECURSION to a view definition in the SFOpen

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