An acronym for Common Table Expression. They are temporary, reusable subqueries that may be recursive.

learn more… | top users | synonyms

3
votes
1answer
39 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
32 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
1
vote
0answers
79 views

How to improve query performance in PostgreSQL? [closed]

I have the following query to be execute within a second but getting time for execute 2.8 minutes: Query: Using Common Table Expression. explain analyze with temp_cte as ( Select ...
-1
votes
1answer
34 views

combining two CTE in a single query

Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select ...
8
votes
1answer
256 views

How do I 'hint' the cardinality of a recursive CTE?

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs: with recursive w(n) as ( select 1 union all ...
1
vote
0answers
140 views

Calculate the compound interest with variable annual rate

I'm moving this question from SO Some years ago I was asked to create something to calculate the current value of a lot of insurance policies, I came out with a query that worked fine, but I was ...
1
vote
1answer
69 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 ...
2
votes
1answer
83 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 ...
2
votes
1answer
91 views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
0
votes
3answers
143 views

Need a faster method to concatenate rows into column(s)--currently using CTEs

Currently, we are using a view which I created that pulls data from a number of tables. The major factor about this is getting the view to specifications involves a mix of CTEs and string ...
0
votes
2answers
128 views

Hierarchy of Employee “names” with CTE: Cannot create a correct version

I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have . And this is my current version: . As you can see there are duplicates and i have ...
9
votes
3answers
304 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, ...
0
votes
1answer
97 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...
0
votes
1answer
84 views

Paging and grouping using CTE?

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a ...
1
vote
1answer
69 views

How do I query a table with many trees of data, for one tree?

I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql? The table looks something like this, =========NAICS_2012========= naics | ...
1
vote
1answer
106 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 ...
4
votes
1answer
59 views

Workaround the snapshot isolation in a writable CTE

The postgres docs say: The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the ...
4
votes
1answer
749 views

CTE memory space [closed]

How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query? I have a CTE that is ...
1
vote
2answers
469 views

From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0. Now I am using features of SQL Server like: Common Table Expressions (I have a table ...
3
votes
1answer
168 views

Avoiding repetition without creating a view

Suppose that I have a query Q1 and I need to run a query like the following: Q1 union select * from (some query that uses Q1 outcome) I would like to do that: Without creating any view Without ...
1
vote
2answers
309 views

Performing SELECT on EACH ROW in CTE or Nested QUERY?

This is a problem in PostgreSQL I have a table which stores the tree of users; +------+---------+ | id | parent | |------+---------| | 1 | 0 | |------|---------| ...
5
votes
2answers
611 views

Oracle View Ignoring External WHERE Clause

Good day, This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
0
votes
0answers
30 views

Running a CTE query in a loop using PL/PGSQL [duplicate]

Possible Duplicate: Running a CTE query in a loop using PL/pgSQL (Cross-post from http://stackoverflow.com/q/12625914/1555778) I'm trying to execute query that is repeatedly called in a ...
2
votes
2answers
847 views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
3
votes
1answer
365 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
4
votes
2answers
1k views

Why should a CTE start with a semi-colon?

I was just looking at a post on SO (http://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table) where Aaron Bertrand proposes using a CTE instead of a numbers table, which ...
4
votes
1answer
365 views

Build a three table join with a recusive table in the middle?

I have three relevent tables: Parts, PartGroup, and MarkupGroup. Parts is simple. PartID artificial primary key Part part number PartGroupID Foreign key sample data: 1 ...
2
votes
1answer
2k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
1
vote
1answer
134 views

Thought CTEs were syntactic sugar

I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive). My understanding was that each CTE reference is executed separately, so there was a ...
6
votes
1answer
1k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
4
votes
4answers
1k 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 ...
10
votes
3answers
7k 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 ...
43
votes
8answers
14k 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, ...
1
vote
4answers
547 views

Top 2 rows per partition from an absolute value date difference

I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images. Location + Camera + image capture_date is the ...
6
votes
4answers
1k 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 ...
4
votes
1answer
674 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 ...
3
votes
1answer
296 views

SQL Server CTE problem

This question is in reference to my last question: SQL Server 08: Union over while I'm writing a CTE to do what was described in that question (find available time slots over a given number of days. ...
2
votes
1answer
233 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
3
votes
2answers
1k views

How can i detect whom a person reports to using a self-referencing employee table?

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect ...
3
votes
2answers
2k views

How to return a CTE as REFCURSOR from an Oracle stored procedure?

I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement. My procedure looked like this: Create or replace Myproc ( MyRefCursor IN OUT SYS_REFCURSOR) as begin ...