Questions tagged [cte]

Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.

Filter by
Sorted by
Tagged with
9
votes
2answers
935 views

Nested CTEs Returning Incorrect Results in Certain Situations

OK, first let me say I got this issue from: Seeking Tsql wrong join explantion in CTE [closed] I was trying to assist to figure out the issue, but got stumped when trying to debug the code one step ...
0
votes
1answer
97 views

How to eliminate duplicates made by recursive CTE [closed]

I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges): CREATE TABLE tmp_deps ( id bigint, code text, name text, start_date date, ...
-1
votes
1answer
78 views

How to execute join statement dynamically without using static group id? [closed]

I work on SQL server 2014 I need to run this statement below dynamically without using group id static because I don't know maximum group id using may be 10,15,20 so How to run query below dynamically ...
3
votes
1answer
128 views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
2
votes
1answer
29 views

I would want, through recursive CTEs, a query to return all cars at tree-shaped, but sorted in each category

Here is both table and data for testing: CREATE TABLE if not exists cars ( id serial PRIMARY KEY, parent_code INT, descr CHARACTER VARYING(50) NOT NULL ); -- Level 1 INSERT INTO cars(...
2
votes
1answer
156 views

How can I change string output in a hierarchy tree with PostgreSQL?

I have a table like this: ------------------------------------------------- | id | description | parent_id | cost -------------------------------------------------- | 1 | Radiology ...
1
vote
1answer
43 views

How to merge multiple rows in one column with hierarchy Postgresql 12?

I have a table like this to save the catalog of 90 services: ------------------------------------------------------------------------------------- | id | service | subservice | description ...
4
votes
1answer
52 views

INSERT duplicate rows for multiple, cascading 1:M child tables

Imagine multiple parent-child tables with 1:M relationships. I would like to "cascade"-insert duplicate rows based on root parent table row selection. Each table has IDENTITY primary key and ...
1
vote
1answer
26 views

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure: CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER); My SQLite version does not support window function,...
1
vote
1answer
49 views

Can CTE simplify repeated and possibly recursive joins?

I have three tables organized as depicted below, showing foreign keys and unique fields (green): That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
2
votes
1answer
60 views

Why can't unused columns and tables be ignored in a recursive CTE

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
1
vote
1answer
79 views

How do I return all column values for the recently inserted row/rows?

I have these two tables in a PostgreSQL 12 database: CREATE TABLE public.test ( entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4() ); CREATE TABLE public.test_registration ( entity_id uuid ...
0
votes
1answer
30 views

Getting Indirect parents

I have following different tables LeafChilds ChildId, ChildName 1, Child1 2, Child2 Group GroupId, GroupName 1, Group1 2, Group2 3, All GroupChildMapping GroupId, ChildId 1, 1 2, 1 2, 2 ...
0
votes
0answers
52 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...
0
votes
0answers
47 views

Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details. Illustrating artificial query is: with data as ( select 5 as some_value from ...
0
votes
1answer
66 views

Recursive CTE to get Grandparent, parent, children [duplicate]

I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents. Here's an example of how things can look in the tree: ...
6
votes
1answer
193 views

String manipulation in Recursive CTE

I am trying to make something like the following work: WITH results(n, string ) AS ( SELECT 1,'lol' UNION ALL SELECT n+1, string + ' lol' FROM results ...
2
votes
1answer
69 views

Org chart CTE with exact index but plan shows expensive sort node

I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
0
votes
0answers
28 views

How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor

Please refer to the following schema at SQL Fiddle when reading the use case below. In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
1
vote
2answers
100 views

Update Column from Recursive CTE (MySQL/MariaDB) [closed]

I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table. My code is below- ...
0
votes
1answer
156 views

Insert multiple rows into a table with id from other table if not exists insert to other table

I have done similar task where I can insert a row into a table if data doesn't exists: WITH user_exists AS ( Select id from users where username='%s' ), user_new ...
0
votes
1answer
32 views

Pivoting 4 by 44 result set

I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each ...
1
vote
1answer
40 views

SQL: Query to insert records into the table if there is not even one record with certain condition

I have a table train_statuses. With the schema as below: CREATE TABLE public.train_statuses ( id uuid NOT NULL, status text NOT NULL, updated_at timestamp without time zone NOT NULL ); ...
0
votes
1answer
34 views

How to improve poor performance with two joined CTE expressions on hierarchical data?

I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
0
votes
1answer
28 views

PostgreSQL skips WITH query

Consider this CTE: WITH division_by_zero AS ( SELECT 1/0 ) SELECT 42 It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?
1
vote
1answer
62 views

Index on a Computed Column(using udf)

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for ...
0
votes
1answer
76 views

How to rewrite slow CTE construction to match speed of temp tables

I think the general advice of this community is to avoid temp tables in favor of CTEs. However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table ...
0
votes
1answer
34 views

PSQL cte delete seem have have ambiguity when select same column from delete table

Both these two sql can be execute , the first one delete all , the second one is logic right. the first one with d0 as ( delete from seller_create_request where seller_id in (...
3
votes
1answer
99 views

Recursive CTE Multiple Levels [duplicate]

I have a table showing which roles recursively grant access to which resources within a database. For example: The Default_Role grants access to the App_Role, the App_Role grants access to the ...
0
votes
1answer
101 views

Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE

I'm trying to implement a simple ABAC system using row level security, with main policy defined as following: CREATE policy resource_access ON resource USING ( ( org_id::varchar = ...
2
votes
2answers
115 views

CTE placement of Where clause

Is there a difference performance wise between these 2 examples: WITH CTE1 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1 WHERE Col3 = '1' ) ...
0
votes
0answers
21 views

Can Redshift process CTE / with chains without making interim tables?

If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables ...
0
votes
1answer
345 views

Getting the “Column name or number of supplied values does not match table definition” when I use CTEs in an IF statement inside a stored procedure

I have a Stored Procedure that has 3 parameters. The stored procedure looks like this: ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '') AS SET NOCOUNT ON DECLARE @...
0
votes
2answers
30 views

Independent aggregation of related entities, while also sorting by related (in a single statement)

I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
0
votes
1answer
108 views

SQL CTE Expression with Cross Join - Can it be simplified?

I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run. The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement. What I'd like to ...
5
votes
2answers
172 views

CTE query that fails to find ancestors

I have a table foo with the content So the records of the table foo can graphically be represented with this graph. When I run a procedure that should return ancestors using @starting_id=9 (...
0
votes
0answers
50 views

Why does my Amazon RDS Postgres server CTE Scan takes much longer than on my local docker postgres server

I just got put on a project to move the local database to AWS RDS. The Queries work great on the local database, but they are extremely slow on amazon RDS. The execution plan is slightly different as ...
2
votes
2answers
88 views

Recursively get ids of tree

I have a table foo. So we have a hiearchical object structure where on foo has one parent (or none) and a list of children. What I want is a sql statement that lists all id:s that can be found either ...
1
vote
0answers
142 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
1
vote
1answer
22 views

Query To Show YTD Total Summing Each Month Prior To Current

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would ...
-2
votes
1answer
49 views

Same query performance problems when using ORDER BY [closed]

I have query that uses a few CTEs. The final output query joins the CTEs together. When I ran the query with no ORDER BY, it runs in seconds. When I ran it with an ORDER BY, it takes several minutes ...
0
votes
1answer
47 views

A way to process a CTE once?

I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references. WITH splits(ID, [NEW TEU]) AS (SELECT s.[DTL ID] AS ...
2
votes
1answer
261 views

Why is my recursive CTE so much slower on Azure SQL?

I have this simple recursive CTE for a hierarchy of folders and their paths: WITH paths AS ( SELECT Id, ParentId, Name AS [Path] FROM Folders WHERE ParentId IS NULL UNION ALL SELECT ...
0
votes
1answer
104 views

calculate or get price of parent items on bill of materials

i want to calculate or get the price of parent items on bill of materials query here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6 please note in the second row, betax has ...
10
votes
1answer
535 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0
votes
1answer
39 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
1
vote
1answer
45 views

Recursive Parent-Child Location Relationship Cache as View

Here is my data structure: CREATE TABLE Locations( LocID int IDENTITY(1,1) NOT NULL, LocationTypeID int NOT NULL, ParentID int NULL, LocNum varchar(50) NULL, LocName varchar(250) ...
0
votes
2answers
1k views

Want to delete then insert with single statement using a CTE in Postgres

I want to delete then insert with single statement using a CTE in Postgres. We could use an explicit transaction and 2 statements, but want to avoid that if possible. I don't know if there will be ...
0
votes
1answer
53 views

List Top Daily Rentals Per Customer using a CTE

I've been trying to learn CTEs and having a bit or trouble getting the hang of them. I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. ...
0
votes
2answers
275 views

mysql - insert multiple values sharing same foreign key

I have the following tables: create table countries ( country_id tinyint unsigned, iso_alpha3_code varchar(4) ); create table provinces ( province_id bigint unsigned, province_name ...

1
2 3 4 5
7