Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
177
votes
7answers
174k views

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, ...
5
votes
1answer
4k views

CTE Error (nvarchar to numeric)

I'm using a CTE to convert SSRS stored proc's into BO stored proc's, as apparently I can't use temp tables with Business Objects. I'm having this query: ;WITH cte1 AS ( SELECT cv.issue ...
13
votes
2answers
9k views

Why should a CTE start with a semi-colon?

I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does ...
2
votes
1answer
2k views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
21
votes
3answers
27k views

Common Table Expression (CTE) benefits?

From msdn : Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. I'm using CTEs quite a lot, but I've never thought deeply about the ...
9
votes
5answers
22k views

Difference between inline view and WITH clause?

Inline views allow you to select from a subquery as if it were a different table: SELECT * FROM /* Selecting from a query instead of table */ ( SELECT c1 FROM ...
15
votes
3answers
2k views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
6
votes
1answer
1k views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
2
votes
1answer
3k views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id (pk), key_name related_key: related_key_id (pk), key_id (fk) First INSERT: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO ...
4
votes
1answer
1k views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
2
votes
1answer
3k views

Add column with a sum total to crosstab() query in PostgreSQL 9.0

Following on from my previous question: Creating crosstab() pivot table in PostgreSQL 9.0 I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
7
votes
1answer
1k views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
12
votes
1answer
9k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
6
votes
3answers
8k views

CTE Running in Infinite Loop

My CTE runs in an infinite loop for a specific customer and I am not able to find out why. Here is the query: ;WITH ClassTree AS (SELECT ID, NAME, Parent_ID FROM ...
16
votes
2answers
8k views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
11
votes
1answer
1k views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling of ...
6
votes
3answers
1k views

How does this derived table with cross apply work?

I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is ...
5
votes
1answer
4k views

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
5
votes
2answers
2k views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...
2
votes
1answer
2k views

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master (...
19
votes
5answers
3k views

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
16
votes
2answers
26k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
10
votes
1answer
568 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 (“...
7
votes
2answers
893 views

CTE column caused an overflow - Order By only!

;WITH cte_Date ( DateCode_FK ) AS ( SELECT DATEADD( DAY, 1 - ROW_NUMBER() OVER ( ORDER BY so1.object_id ), GETDATE() ) FROM sys....
3
votes
1answer
602 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
3
votes
1answer
490 views

CTE query doesn't print the right structure?

I have this table which represents hierarchy : childID parentID NAME siblingOrder 1 1 a 0 212 1 ab 1 112 ...
2
votes
1answer
379 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
3answers
4k views

SQL Server: Improve CTE performance with case statements

I need your help, I need some guiadance to improve the performance for the following given view. I have a view writen with the below code: with timeframes as ( select p.SEARCH_NUM, case when p....
1
vote
1answer
147 views

Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking: One important query which takes 100ms on version 11 (same VM) ...
1
vote
3answers
169 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 ...
1
vote
0answers
343 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/...
0
votes
1answer
48 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...