Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [cte]

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

18
votes
4answers
1k views

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
1
vote
0answers
23 views

Guarding against CTE infinite loops in JOIN condition considered bad practice?

In docs, and pretty much everywhere else on the net, to guard against loops in recursive CTEs people use cycle column. The problem with this solution (as opposed to doing the ANY(path) check in join ...
0
votes
2answers
38 views

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice? CREATE TEMPORARY TABLE folders (...
0
votes
1answer
62 views

Generate Dates between Date Ranges in mysql

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9. Is there an inbuilt function which I ...
2
votes
1answer
79 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
1
vote
0answers
33 views

Unrolling an entity-matching table

I have a table with the following structure: create table pairs ( block text, left_entity int, right_entity int ); That table was the result of a record linkage process. One of the ...
0
votes
1answer
37 views

subtract missing Value from Last Value

Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me. I have a source-Table as following: CREATE TABLE [dbo].[Mailbox]( [...
1
vote
1answer
49 views

Performance: CTE in an “IN”-Operator

I've got a query with a CTE (getting all children of a parent) followed by a main statement using the "IN"-Operator to filter for the CTE's output. Roughly looks like this: WITH cte_table AS (...) ...
2
votes
1answer
37 views

WITH RECURSIVE with union among the recursive term

Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules: And I ...
4
votes
3answers
513 views

Caching intermediary CTEs for multiple uses

I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'...
10
votes
1answer
351 views

What Rules Determine When SQL Server Use a CTE as an “Optimization Fence”?

A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL: Two Important Differences Between SQL Server and PostgreSQL The first point (“...
0
votes
1answer
35 views

Query Locks Table And Takes Long Time to Run

I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick. However, in production it seems to hang and causes blocking of other queries. ...
1
vote
1answer
41 views

Delete duplicates and reroute referencing rows to new master

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's and name's: ╔════╤═════════╤══════════╗ ║ id │ user_id │ ...
1
vote
1answer
85 views

Distribute multiple payments to invoice lines

I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014 Data looks like this: Invoice lines table (sales): lineId invoiceId value 1 1 100 2 ...
1
vote
0answers
24 views

Postgres recursive query not using trivial index on massive table

I've got a simple recursive query to find a tree of all posts: with recursive all_mentions(post_id, author_id, mentioned_id) as ( select post_id, author_id, mention_id from mentions where ...
5
votes
3answers
890 views

Does the 'quality' of query optimization differ by database provider? [closed]

I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in ...
3
votes
0answers
157 views

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
0
votes
1answer
74 views

Better approach to optimize query

We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following. Here is the query... WITH CTE1 ( COL1, COL2) AS (...
1
vote
3answers
78 views

SQL recursion and cte - dates timeseries

I need to build vector(s) of time series dates (say 10) for a subset of start dates. The following statement returns 10 consecutive dates starting from '2010-01-01'. ;with cte as (select 1 i union ...
1
vote
1answer
30 views

Sql query for all ascendent of a node

I have a table having records as below: ID Name parentId 1 A Null 2 B 1 3 C Null 4 D 3 5 E 4 I need out put in following format ID Name ParentName ...
1
vote
0answers
60 views

View using WITH clause Function [closed]

I have written a view which is using a with clause having a function something like this: create or REPLACE view abc as with function calculator (m number, r number) return number is begin ...
0
votes
1answer
70 views

Am I compromising effiency by using CTE instead of indexed tables? [closed]

Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well. The ...
0
votes
1answer
42 views

Create rows groups by using subgroups limits

I had originally posted this in SO but reasoned that it would be more appropriate here. I have a view in an sqlite database such as this: SQL Fiddle (http://www.sqlfiddle.com/#!5/ae95b/1) This ...
31
votes
2answers
3k views

If a CTE is defined in a query and is never used, does it make a sound?

Do unused CTEs in queries affect performance and / or alter the generated query plan?
0
votes
0answers
335 views

Slow performance using CTE

The query looks something like this ; with stats as ( select * from openquery ([remote-server], 'select empid, stat_dt' from [statistics]') -- when run solo, returns 20K rows in 45 seconds ) , ...
5
votes
1answer
186 views

How to optimize a keyset pagination query with CTEs on a big table?

I tried to document myself as much as I could on the topic before coming here to bother you, but here I am anyway. We want to implement keyset pagination on this table: create table api.subscription ...
3
votes
1answer
354 views

What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
2
votes
2answers
77 views

Oracle grouped row counts to columns

I have a table which contains this data: - ID - PRIMARY KEY, INTEGER - PC_ID - INTEGER - USER_ID - INTEGER - PAYMENT_DATETIME - TIMESTAMP - FIO - VARCHAR2(255) When I do a SELECT I get this (example)...
1
vote
0answers
52 views

How to remove duplicate records from result set of owner

I'm working with one project like rental agreement system. There are two roles types of users (User Role) Signer = 0 Observer = 1 Anyone can register and create a document(which will be referred as ...
0
votes
2answers
41 views

Select starting node ID in CTE

I have following table: create table osoba( id int primary key, idNd int, nazwim nvarchar(500), ) Data looks like below: id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 ...
2
votes
1answer
105 views

What happens with WITH if later the query fails

I am by no means a database expert so I might be asking something dumb. So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a ...
0
votes
0answers
43 views

Alternative to Mutli-line Table Valued Functions

I currently have a relatively large MTVF returning a set of data, the code I have provided below is a small subsection of what I have in place. All but 1 part of my function is built from a Cte chain ...
0
votes
1answer
21 views

Using WITH clause with INSERT statement in POSTGRESQL

I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table. Example: If the cust_id='11' then I need to get the ...
1
vote
3answers
89 views

PostgreSQL - maximum of sum over different months with ties over multiple years

This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the ...
3
votes
1answer
71 views

PostgreSQL priority between CTE and real table

Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which ...
2
votes
1answer
50 views

Is non-dynamic scalar subquery evaluted for each row?

Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless? SELECT /* ... */ FROM t1 WHERE parent = ( ...
6
votes
1answer
165 views

Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
0
votes
2answers
1k views

Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int

I'm getting the following error : Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int. I have tried casting the problematic field which is in my third CTE(CTE4)...
0
votes
1answer
321 views

UPDATE using CTE

I am trying to update the single table with 4 columns (run_id, fqcn, diff_value, cleared_run_id) with the following snippet: WITH cte AS ( SELECT diff.fqcn, diff.run_id, diff.cleared_run_id, ...
1
vote
0answers
135 views

Implementing a graph-like design on postgresql using CTEs and inheritance

This question is about attempting to model/design an elementary graph-like database within postgres. The starting point is that graphs have: Nodes Nodes have properties Node are connected by (bi/...
5
votes
1answer
518 views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...
1
vote
1answer
33 views

Very weird result of CTEs and subqueries [closed]

WITH t1 AS (SELECT a.id, AVG(standard_qty) std_avg, AVG(poster_qty) pos_avg, AVG(glossy_qty) gloss_avg FROM accounts a ...
1
vote
1answer
2k views

Oracle update statement using with clause takes for ever

I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below. update final_table set(destination_col1,destination_col2)= ( select c4,c5 ...
1
vote
0answers
94 views

Calculating the Path of a Row in an Adjacency List When Recursive CTE Are Not Supported

I'm currently transitioning a SQL backend from SQLite to CockroachDB, the latter being a semi-PGSQL-compatible database. I have multiple tables which at their core are adjacency lists, i.e.: CREATE ...
0
votes
1answer
236 views

How can I get performance using PostgreSQL CTE recursive?

I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by ...
0
votes
1answer
171 views

group by excluding a column

I have this code: insert into [dbo].[NGC_Agent_Intervals] SELECT AgentID, date, [Hour] = DATEPART(HOUR, RSRange), hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(...
7
votes
2answers
1k views

Simple SQL CTE update

I am a little stumped with this CTE update stmt: DECLARE @a TABLE (ID int, Value int); DECLARE @b TABLE (ID int, Value int); INSERT @a VALUES (1, 10), (2, 20); INSERT @b VALUES (1, 100),(2, 200); ...
2
votes
2answers
4k views

SQL server create temporary function as a CTE

I need to create a temp function, for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables). Is there any way that I could use a CTE or a #temp query ...
1
vote
1answer
1k views

Merge statement with two conditions in same CLAUSE alternative in Postgres

I was able to convert a Merge which has one condition in both MATCHED & UNMATCHED clauses using CTE. But not sure how to do (both DELETE & UPDATE), if I have two conditions in the same clause. ...
1
vote
1answer
265 views

Oracle CTE to SQL Server Using SSIS OLE DB Source

I wrote a cte against an Oracle database and the query executes without issue when working in Oracle SQL Developer. I need to use ssis to transport the results of that query to a SQL Server database. ...