0

Good day everyone! Firstly, I'm sorry for my poor english. Well, I've got a question that you can read in the title of this message.

SQL Server returns this message(Error 253) when I'm trying to select necessary data.

Translate "Recursive element from CTE (which name is 'recurse' - my note) has multiple reference in CTE.

How can I solve this problem?

Can you advice me how to join two tables (with 2 columns(for example : a and b) which are the result of previous recursive select (I'm writing about the same select, but about another iteration of if)

    with recurse (who_acts,on_whom_influence)
as 
(
-------------------------------------------FIRST SELECT
select  distinct interface_1.robot_name as who_acts,interface_2.robot_name as on_whom_influence
from INTERFACE as interface_1,INTERFACE as interface_2
    where (interface_1.number in (                                  select  INPUT_INTERFACE.source
                                        from INPUT_INTERFACE
                                )
            and interface_2.number in (
                                        select INPUT_INTERFACE.number 
                                        from INPUT_INTERFACE
                                        where (INPUT_INTERFACE.source=interface_1.number )
                                     )
          )

-------------------------------------------RECURSIVE PART   
union all
select rec1.who_acts,rec1.on_whom_influence
from recurse as rec1
inner join 
(select rec2.who_acts,rec2.on_whom_influence
from recurse as rec2) as P on (1=1)
)
select  * from recurse

The problem is in recurse CTE.The connecting condition is not simple, but it have no influence on this problem. Can you type some working code in comments

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
NDGO
  • 51
  • 1
  • 1
  • 8
  • 2
    Can you **SHOW US** the query in question, and explain the tables involved, and their structures? – marc_s May 10 '13 at 12:23
  • I have just added it.perhaps this code doesnot work but I cant write code all and debug its all. – NDGO May 10 '13 at 12:32
  • The first select returns me correct set of data and MS SQL swears on the second part – NDGO May 10 '13 at 12:36
  • Can you add some sample input data and their expected output to the question? As it stands, your query does not appear to need a *recursive* query at all. –  May 10 '13 at 12:56
  • You don't **always** have to solve problems in one query. Temporary tables and query batches work a lot better for (1) maintainability (2) query plan robustness (3) optimizability, e.g. clustering on the temp table – RichardTheKiwi May 10 '13 at 13:06

1 Answers1

2

Here's a dummy table

create table tbl1 ( a int, b int );
insert tbl1 select 1,2;
insert tbl1 select 11,12;
insert tbl1 select 2,3;
insert tbl1 select 4,5;

And a similar query to yours

with cte as (
  select a,b from tbl1
  union all
  select x.a,x.b from cte x join cte y on x.a=y.a+1
)
select * from cte;

The error:

Recursive member of a common table expression 'cte' has multiple recursive references.: with cte as ( select a,b from tbl1 union all select x.a,x.b from cte x join cte y on x.a=y.a+1 ) select * from cte

Basically, the error is exactly what it says. You cannot have a recursive CTE appear more than ONCE in a recursive section. Above, you see CTE aliased as both x and y. There are various reasons for this limitation, such as the fact that CTEs are recursed depth-first and not generation-by-generation.

What you should think about is why you would need it more than once. Your recursive portion doesn't make sense.

select rec1.who_acts,rec1.on_whom_influence
from recurse as rec1
inner join 
(   select rec2.who_acts,rec2.on_whom_influence
    from recurse as rec2) as P on (1=1)

On the surface, the following are true if recurse were a real table (non-CTE):

  1. The number of rows generated is count(recurse as [rec1]) x count(recurse as [rec2]).
  2. The rows in recurse (rec1) are each replicated per row in recurse, hence #1
  3. Columns from rec2 are never used. rec2 serves only to multiply

If this were permitted to run, the recursive portion of the query would keep quadratically increasing its number of rows and never finish.

Community
  • 1
  • 1
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • I tried to post the code from 2 first code blocks in your message. Well, SQL server is swears on your code. SQL returns "the same error" – NDGO May 10 '13 at 13:05
  • "If this were permitted to run, the recursive portion of the query would keep quadratically increasing its number of rows and never finish." YES, but I've got an algorithm but sql constraints cant allow me to realize it in sql without this step – NDGO May 10 '13 at 13:07
  • That's exactly what I am claiming in the very next sentence. "The error:..." and the explanation followed. It is simply not allowed, and I have yet to *required* such a syntax. – RichardTheKiwi May 10 '13 at 13:07
  • `YES, but I've got an algorithm but sql constraints cant allow me do it` ? So you have an algorithm to generate an infinite number of rows, and SQL Server doesn't let you - and that's a problem? – RichardTheKiwi May 10 '13 at 13:08
  • ok. If I could use CTE more than 1 time. IF I counld join this 2 CTEs...IN my algorithm recursive select stops when the previous select returns the same number of rows as current – NDGO May 10 '13 at 14:59
  • Im not allowed to do such things in SQL,am I? – NDGO May 10 '13 at 15:18
  • the answer you can find here [returning-all-children-with-a-recursive-selectre](http://stackoverflow.com/questions/16486015/returning-all-children-with-a-recursive-selectre) – NDGO May 10 '13 at 16:18