CODEWITHSUNDEEP
sqlsql-server
kat14
kat14

Reputation: 23

The statement terminated. The maximum recursion 100 has been exhausted before statement completion error

I have a query that converts a date to monthly and inserts it into a table but I am getting this error not sure how to fix it?

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Here is my query:

with cte as (
    select datefromparts(year(DUC_Start), month(DUC_Start), 1) dt, API, Well, Well_Num, Operator, County, 
    State, DI_Basin, DI_Play, Producing_Reservoir, MD, Production_Type,     Well_Status, Spud_Date, 
    Completion_Date, DUC_Start, DUC_End, Drill_Type, GPI, Lateral_Length, First_Prod_Date, Section, 
    Township, Range, Abstract, Block, Survey, SH_LATITUDE, SH_LONGITUDE, BH_LATITUDE, BH_LONGITUDE
    from Historical_Duc_Data t
    union all 
    select dateadd(month, 1, dt) dt, API, Well, Well_Num, Operator, County, State, DI_Basin, DI_Play, 
    Producing_Reservoir, MD, Production_Type,   Well_Status, Spud_Date, Completion_Date, DUC_Start, 
    DUC_End, Drill_Type, GPI, Lateral_Length, First_Prod_Date, Section, Township, Range, Abstract, Block, 
    Survey, SH_LATITUDE, SH_LONGITUDE, BH_LATITUDE, BH_LONGITUDE
    from cte c 
    where c.dt <= DUC_End - 30
)

INSERT INTO [dbo].[Historical_Ducs_By_Basin] (
    [dt],
    [API],
    [Well],
    [Well_Num],
    [Operator],
    [County],
    [State],
    [DI_Basin],
    [DI_Play],
    [Producing_Reservoir],
    [MD],
    [Production_Type],
    [Well_Status],
    [Spud_Date],
    [Completion_Date],
    [DUC_Start],
    [DUC_End],
    [Drill_Type],
    [GPI],
    [Lateral_Length],
    [First_Prod_Date],
    [Section],
    [Township],
    [Range],
    [Abstract],
    [Block],
    [Survey],
    [SH_LATITUDE],
    [SH_LONGITUDE],
    [BH_LATITUDE],
    [BH_LONGITUDE]
)

    enter code here

select * from cte

Upvotes: 2

Views: 1141

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The specific fix to your problem is to add option (maxrecursion) to the end of the query:

select *
from cte 
option (maxrecursion 0);

This assumes that the CTE is behaving correctly. You are generating rows by month, and 100 months is a fair number of years. If you are not expecting time periods of more than 12 years, then you might have a bug in your code.

Upvotes: 3

Related Questions