An acronym for Common Table Expression. They are temporary, reusable subqueries that may be recursive.
0
votes
2answers
90 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
66 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 ...
0
votes
2answers
83 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 |
|------|---------|
...
4
votes
2answers
169 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
29 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
479 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
146 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 ...
3
votes
2answers
602 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 ...
3
votes
1answer
223 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
819 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
107 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
503 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
466 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 ...
4
votes
2answers
2k 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 ...
18
votes
6answers
4k 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, ...
