Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You are being catched in a loop because first you select this row:</p> <pre><code>2 1 2 '2012-01-22' </code></pre> <p>then </p> <pre><code>2 2 3 '2012-01-10' </code></pre> <p>then </p> <pre><code>2 3 1 '2012-01-05' </code></pre> <p>and then first line again and again. You need to check is your data consistent enough for your report or not.</p> <p>Or, maybe you need to add following condition to your CTE:</p> <pre><code>ct.previous_status_id &gt; sc.previous_status_id </code></pre> <p>Or, you can change your script by addind parameter that is controling the level of recursion like that:</p> <pre><code>with change_tree as ( SELECT sc.order_id, sc.previous_status_id, sc.next_status_id, cast(sc.next_status_id as varchar(max)) as status_path, sc.Create_Date as StartDate, sc.Create_Date as EndDate, 1 AS deep FROM status_change sc WHERE previous_status_id = 1 AND order_id = 2 UNION ALL SELECT sc.order_id, sc.previous_status_id, sc.next_status_id, ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) sc.Create_Date as StartDate, st.Create_Date as EndDate, ct.deep + 1 AS deep FROM status_change sc JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id WHERE deep &lt; 3 ) SELECT * FROM change_tree WHERE status_path = '1,2,3'; </code></pre> <p>Or, change your script in this way (this will restrict your recursion from going in wrong ways):</p> <pre><code>with change_tree as ( SELECT sc.order_id, sc.previous_status_id, sc.next_status_id, cast(sc.next_status_id as varchar(max)) as status_path, sc.Create_Date as StartDate, sc.Create_Date as EndDate FROM status_change sc WHERE previous_status_id = 1 AND order_id = 2 UNION ALL SELECT sc.order_id, sc.previous_status_id, sc.next_status_id, ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) sc.Create_Date as StartDate, st.Create_Date as EndDate FROM status_change sc JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id WHERE '1,2,3' LIKE ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) + '%' ) SELECT * FROM change_tree WHERE status_path = '1,2,3'; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POTSQL recursion - max recursion error with duplitace values in table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAndrey Gurinov
    1. USAndrey Gurinov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTSQL recursion - max recursion error with duplitace values in table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThis is the exact situation I'm having. I'll check Your answer. One more thing - how to do that report incrementally? So that my procedure won't have to go through whole status_change table every night? - @Andrey Gurinov
      singulars
      1. PO
      1. USMisiu
    2. COI believe that could be a subject for another question. But why do you worry about full reprocessing of your table? Is it so big? Does processing take a lot of time?
      singulars
      1. PO
      1. USAndrey Gurinov
    3. COI have about 800k+ rows and about 30 patterns to search (different status_path combination). So I think that doing every combination for every row gives me about 24 million operations. But orders are still being added to table and my patterns also grow in number. Report in type 'on demand', so user clicks button and moments later he should get that report. That's why I think that aggregation table is necessary. If I'm wrong please correct me :) @Andrey Gurinov
      singulars
      1. PO
      1. USMisiu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload