Recursive References are not allowed in Sub_queries T-SQL

Question

I have a CTE query like so:

declare @stop datetime
set @stop='2014-12-12 00:00:00.000'

declare @start datetime
set @start='2011-12-12 00:00:00.000'

declare @temp datetime
set @temp=@start


with X(A,B) AS(
     select @Temp as A,count(*) as B
          from Table1
               where left(convert(varchar,Table1Row1,120),4)=left(convert(varchar,@Temp,120),4)
      union all select dateadd(year,1,(select X.A from X)) as A,count(*) as B
          from Table1
               where left(convert(varchar,Table1Row1,120),4)=left(convert(varchar,(select X.A from X),120),4)
               and datediff(YYYY,(select X.A from X),@stop)>0)
      select A,B into #TempTbl1 from X option(maxrecursion 32767);

I'm trying to get the count of rows for each year from @start to @stop. The query accepts the select X.A from X at the select statement but not at the where clause.

I'm getting a compile time error stating: Recursive Member of a Common table expression 'X' has multiple recursive references.

On executing, I'm getting the error recursive references are not allowed in sub-queries. But, I've just referenced it in the select query where it shows no error. Is there a syntactical problem, or am I just not allowed to reference the table there?

Where is your datetime column in the Table1? What is the relation between @Start, @Stop and your table? — alzaimar
– alzaimar, Commented Mar 24, 2014 at 8:15
@alzaimar: In table1, Table1Row1 is the datetime column. Start and Stop are two datetimes which indicate the start and stop years. ie from 2012-12-01 00:00:00.000 to 2012-12-01 00:00:00.000 — Mkl Rjv
– Mkl Rjv, Commented Mar 24, 2014 at 8:44

Edwin Stoteler · Accepted Answer · 2014-03-24 08:14:36Z

1

select dateadd(year,1,(select X.A from X)) as A,count(*) as B
          from Table1
               where left(convert(varchar,Table1Row1,120),4)=left(convert(varchar,(select X.A from X),120),4)
               and datediff(YYYY,(select X.A from X),@stop)>0

You select X three times in the query. Try doing a join from on Table1 and X instead of 3 subquery's.

Also I think this query can be done allot easier. Something like:

SELECT DATEPART(year, datecolumn), COUNT(pk)
FROM Table1
WHERE datecolumn between @startdate AND @enddate
GROUP BY DATEPART(year, datecolumn)

edited Mar 24, 2014 at 8:14

answered Mar 24, 2014 at 8:07

Edwin Stoteler

1,2271 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mkl Rjv Over a year ago

I get it, when I try using X in a query just once, it works. Why is it like that?

Mkl Rjv Over a year ago

And I know it can be done easier, just wanted to try out some CTE methods.

alzaimar · Accepted Answer · 2014-03-24 08:18:14Z

0

As mentioned in my comment, I don't see a relation with the date range and your Table1. I would assume some kind of a date or datetime column.

If your table would have column to use, you could simply execute the following query.

select DatePart (year,Table1DateTimeColumn) as Year,
        count (*) as Cnt
  from Table1
 where Table1DateTimeColumn between @Start and @Stop
 group by DatePart (year,Table1DateTimeColumn)

answered Mar 24, 2014 at 8:18

alzaimar

4,6321 gold badge22 silver badges31 bronze badges

1 Comment

Mkl Rjv Over a year ago

Yes, I understand that there is an easier way, just I'm trying to read up on Common Table Expressions, and understand how they function.

Collectives™ on Stack Overflow

Recursive References are not allowed in Sub_queries T-SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related