CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expression
Mkl Rjv
Mkl Rjv

Reputation: 6933

Recursive References are not allowed in Sub_queries T-SQL

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?

Upvotes: 0

Views: 2416

Answers (2)

alzaimar
alzaimar

Reputation: 4622

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)

Upvotes: 0

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

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)

Upvotes: 1

Related Questions