Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
47 views

Recursive CTE to get Grandparent, parent, children

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
157 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
66 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
25 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
60 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
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
36 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
27 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
56 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
73 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
23 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
88 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
37 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
93 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
17 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
221 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
27 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
80 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
169 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
36 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
63 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
84 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
21 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
189 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
103 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
463 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
31 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
462 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
50 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
77 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 ...
3
votes
3answers
67 views

Select rows based on values from 1 column being the same?

An example of my database table film id | scene id | value id ----------------------------- 0 | 0 | 7 0 | 1 | 1 0 | 1 | 0 1 | 2 | 8 ...
3
votes
1answer
95 views

Get the total count of every child foreign keys

I have been trying this in SQL EXPRESS 2016. Here is my problem: I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table. ...
1
vote
0answers
11 views

Propagate where condition into CTE of a view, using index?

I have written a multi-step view using CTEs. The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that ...
2
votes
1answer
44 views

How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp ...
0
votes
0answers
28 views

Recursive sum of children with calculated column

I'm trying to create a recursive query to determine the total cost of a multi-level bill of materials (kind of like a baking recipe list, that has sub-recipes). The top level bill of materials could ...
0
votes
2answers
37 views

how to view data as history of field value change

Example i have data like id employeeId Salary Year 1 1 12000 2001 1 2 10000 2001 1 1 16000 2002 1 3 12000 2004 1 ...
1
vote
0answers
30 views

data-modifying CTEs and triggers

If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run? -- t1 and t2 both have insert ...
0
votes
1answer
33 views

SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should

CTE SQLFiddle I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from ...
2
votes
2answers
397 views

WITH … AS statement not working as expected

I get this error: It arises when I try to execute the following query: with expensive_service as ( select s1.* from service s1, service s2 where s1.price > s2.price ) select * from ...
2
votes
2answers
314 views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
1
vote
0answers
118 views

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that ...
0
votes
0answers
21 views

Splitting a large table using CTE with a constraint

I have a large table called "order". That table has a field record_type. I want to split the table up into different orders by record type. All of the tables for the different types of orders have the ...
1
vote
1answer
55 views

SQL Conditional Join - remove duplication from Stored Procedure

This stored procedure accepts a search parameter. I want to join on a table valued function if the search parameter is not null. The way I have written it works, but it seems like lots of ...
5
votes
1answer
923 views

Why am I getting nondeterministic results with the deterministic function STDEV()?

This is type of query I'm trying to run: WITH CTE_Ordered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId FROM SourceTable ), ...
5
votes
1answer
1k views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
-2
votes
1answer
60 views

Common Table Expressions [closed]

Are there alternatives to common table expressions in Oracle 12 c? I'v been using the syntax from SQL Server and oracle gives me the following error. SELECT CTE_TEST AS (SELECT DISTINCT deptno, ename ...

1
2 3 4 5
7