Stack Overflow
Stack Overflow

Questions tagged [recursive-cte]

A Recursive Common Table Expression in SQL is a Common Table Expression (CTE, also known as "WITH clause") that refers to itself recursively. The temporary result set is usually computed by iteration. Use this tag for questions concerning the definition and use of recursive CTEs.

133 questions
1
vote
0 answers

SQL Server Express - TVP Parameterized TVF with recursive CTE freezing at execution

Just came across a very strange server behavior - namely: I got TVF with one TVP argument as below CREATE FUNCTION [BUD].[Foo] (@IdAgrs IdTable READONLY) RETURNS TABLE AS RETURN ( WITH recursive_CTE AS ( --> Select here is…
L5k
  • 23
  • 4
1
vote
1 answer

How can I recursively calculate a value

I have this table. Bundles id | parent_id | quantity 1 | 0 | 1 2 | 1 | 4 3 | 2 | 5 I want to get the total quantity of a bundle with id 3, which is 1 * 4 * 5 = 20 items Can this be done with a single query?
Loupax
  • 4,728
  • 6
  • 41
  • 68
1
vote
1 answer

Is there a set-based solution for executing an rCTE over multiple rows?

I have a table that stores a number of rows, each declaring a point (x, y). Each row references the next point in a pattern, so the query to retrieve an entire pattern is recursively defined through a linked list structure. I've developed a rCTE to…
Dan
  • 3,246
  • 1
  • 32
  • 52
1
vote
1 answer

Recursive chain with JOIN element?

I recently asked for a recursive query in postgreSQL. The answer fit well, but as soon as I add a JOIN element, it gives me the following error: ERROR: missing FROM-clause entry for table "n": WITH RECURSIVE chain AS ( SELECT n.pordnr,…
Trollwut
  • 541
  • 1
  • 7
  • 23
1
vote
1 answer

Converting a script from MSSQL to PL/pgSQL

I just started working with the EVE static dump, which is just a lot of tables with data about the game, such as a list of what solar systems connect, which is what I'm dealing with. I want to make a webpage that lets you filter out systems, and the…
mtfurlan
  • 1,024
  • 2
  • 14
  • 25
0
votes
1 answer

Multiple column Tree Hierarchy (Manager, Employee) in Oracle Sql

I have an employees table as below…
Alexis Graham
  • 1
  • 1
0
votes
2 answers

Sum a column using Recursive query in Redshift

Question I've got a Redshift table which contains hierarchal data by month. I am trying to calculate sum for manager based on people under them but struggling to get the SQL working. Actual data contains multiple levels. Example Sum of Bob's…
AIViz
  • 82
  • 9
0
votes
1 answer

Numbered days back from given date, only including certain date ranges

I have a temporary table #input which holds an ID and a calculation date cdate. I have another temporary table #service which holds a service record for each ID. The field ‘countable’ on the #service table corresponds to whether a period of service…
vinstra_82
  • 36
  • 3
0
votes
0 answers

Handling Stock Adjustment in Recursive CTE for Recipes with Shared Ingredients

I am working on a SQL Server 2016 database where I have recipes and ingredients stored in separate tables. The relationship between recipes and ingredients is stored in a junction table. I am using a recursive common table expression (CTE) to…
Santiago Jose Gianotti
  • 1
  • 1
0
votes
1 answer

Why does a simple recursive CTE make PostgreSQL resort to an external sort?

Using PostgreSQL 14, I'm working on a table workplaces which models a tree of workplaces. Besides attributes like country_code, each workplace identifies its parent workplace via a parent_id foreign key (which is NULL in case there is no…
Frerich Raabe
  • 90,689
  • 19
  • 115
  • 207
0
votes
0 answers

Mysql - "With recursive" not working with PHP

I'm trying to print from a db the categories in a hierarchy order. I have developed a mysql code that sort of works in dbfiddle (https://dbfiddle.uk/_oUW-eW_), but I am not able to make it run in PHP. The mysql code is the following: with recursive…
Andrealuigi
  • 19
  • 3
0
votes
2 answers

Using a Table Valued Function inside a Recursive CTE

I am trying to use a recursive CTE to combine multiple calls to a table valued function. I have a fiddle at: https://dbfiddle.uk/kktqHivz I have a table called sales: create table sales ( date date, total decimal(6,2) ); The function…
Manngo
  • 14,066
  • 10
  • 88
  • 110
0
votes
2 answers

Recursive CTE for a family tree keeps going into an infinite loop

I am trying to design a RDBM model for a family tree using SQL Server and currently I have the 3 tables that looks somewhat as follows Members - Stores basic details of members of the family. --------------------- | ID | Firstname…
Ranjith R Shenoy
  • 43
  • 5
0
votes
1 answer

Recursive CTE Pivot for rolling count in SQL Server?

Basically I have a table of membership dates and status changes and I want to create a rolling count of each number of members by status for each date like: And I want to output it like: Not really sure on the best way to construct this query I…
Benzo
  • 13
  • 4
0
votes
1 answer

Recursive CTE- Gives me dupes of 3 rows

I am trying to find a group account and populate the aggregation of its child accounts QTY and Market value using CTE and Recursive CTE.. it gives me the correct result 3 times.. Not sure what i am missing here. Scenario: Example Composite account…
Mohan
  • 61
  • 1
  • 1
  • 5
1 2 3
…
8 9