An acronym for Common Table Expression. They are temporary, reusable subqueries that may be recursive.
3
votes
1answer
24 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
568 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 ...
0
votes
2answers
134 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
69 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
126 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
205 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
501 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
152 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
666 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
241 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
940 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
111 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
536 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
518 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 ...
5
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 ...
28
votes
6answers
5k 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
334 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 ...
5
votes
4answers
547 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
534 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
263 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
217 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
847 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
1k 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
...
