common-table-expression Questions
7
Solved
Consider the following simple DAG:
1->2->3->4
And a table, #bar, describing this (I'm using SQL Server 2005):
parent_id child_id
1 2
2 3
3 4
//... other edges, not connected to the su...
Scour asked 6/5, 2009 at 13:20
4
Solved
I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I've simplified the data for sake of example):
Es...
Yee asked 7/8, 2013 at 15:4
3
Solved
I have posted something similar before, but I am approaching this from a different direction now so I opened a new question. I hope this is OK.
I have been working with a CTE that creates a sum of...
Hekate asked 2/4, 2014 at 9:33
6
Solved
SQL is obviously not meant for this but I'm wondering if it's possible to do this with SQL as a sort of challenge. Given an arithmetic parse tree such as:
7-2*3
Which could be represented as:
-
...
Schrecklichkeit asked 21/3 at 20:48
4
Is there any way to build a CTE for a list of hard coded values? For example, I have a list of known IDs (i.e. 101,102,105,200...), how would I be able to create a CTE with one column called ID but...
Inexpiable asked 3/4, 2014 at 22:46
1
I have working SQL code that have two CTE and one of them must work in parallel with main statement like that:
WITH RECURSIVE cte1 AS (...get initial rows...),
cte2 AS (...select rows from cte1 an...
Paestum asked 29/11, 2018 at 17:53
4
I am using SQL server 2012. I have three CTEs defined in a row as shown below:
;WITH X_CTE (A, B, C, D)
AS (
...
)
,
Y_CTE (A, B, C, D)
AS (
...
)
,
Z_CTE (A, B, C, D)
AS (
...
)
Then, I inse...
Maeganmaelstrom asked 18/12, 2013 at 1:27
5
Solved
What's the CTE syntax to delete from a table, then insert to the same table and return the values of the insert?
Operating on 2 hours of sleep and something doesn't look right (besides the fact th...
Felker asked 17/6, 2015 at 21:10
1
I need to traverse a node tree in pre order to generate a menu listing and so far I've come up with this recursive CTE:
WITH RECURSIVE nodes AS (
SELECT '' as spacer,
1::numeric as level,
(rank...
Fieldwork asked 11/12, 2020 at 8:17
3
Solved
I'm trying to find the best way to do a Postgres query with Common Table Expressions in a Rails app, knowing that apparently ActiveRecord doesn't support CTEs.
I have a table called user_activity_...
Kabyle asked 15/12, 2014 at 19:46
2
Solved
Here: (To find infinite recursive loop in CTE) is a discussion how to prevent an infinite loop in a recursive query. There the recursion is prevented on the "query level" - at least in an...
Maeda asked 25/6, 2018 at 14:14
5
Solved
I was wondering if this was possible. I have an existing query that uses the WITH clause to apply some aggregated data to a SELECT query like so: (massively simplified)
;WITH alias (y,z)
AS
(
SELE...
Schematic asked 22/3, 2013 at 15:37
5
I have written a following query using CTE in Postgres. Now I am unable to delete records from it.
WITH cte AS (
SELECT
firstname,
lastname,
country,
ROW_NUMBER() OVER (
PARTITION BY
...
Titustityus asked 2/3, 2021 at 7:14
3
Just using this simple code to create CTE, in the end I want to create more complex code, that's why I am using CTE.
Problem Link
WITH TOTAL_SUBMISSIONS AS(
SELECT * FROM View_Stats)
SELECT * FROM...
Omnipotence asked 5/5, 2022 at 23:4
4
Solved
I am converting some SQL Logic from T-SQL used in SSMS to Amazon Redshift. I believe Redshift is a fork of Postgres version 8.0.2 so the below may not be possible unless using Postgres 9.1.
WITH ...
Remediosremedy asked 26/3, 2018 at 19:31
3
Solved
Hibernate 6.0.1 with PostgreSQL JDBC driver 42.3.5 causes the following exception:
java.lang.UnsupportedOperationException:
CteInsertStrategy can only be used with Dialects that support CTE that ca...
Potentiometer asked 10/5, 2022 at 20:51
1
Solved
Common Table Expression is a fairly common practice in different RDBMS (PostgreSQL, MySQL, Oracle, SQLite3 etc.) to perform the same calculation multiple times over across multiple query components...
Ariannearianrhod asked 9/2, 2023 at 1:16
3
Solved
I've already read this, this and this, but I cant make this SQL work:
INSERT INTO main_phrase (description) VALUES ('Mot commun féminin pluriel animaux');
/* ERROR: */
WITH
t1 AS (
SELECT id
F...
Seductress asked 9/1, 2016 at 0:11
5
Solved
Can I add an INDEX to a Common Table Expression (CTE)?
Stupefy asked 22/10, 2010 at 10:31
2
Solved
I'm going crazy trying to get UPDATE to work with a CTE in MySQL.
Here's a simplified schema of sa_general_journal:
CREATE TABLE `sa_general_journal` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMEN...
Seals asked 16/12, 2020 at 5:18
5
Solved
I'm looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can't use ...
Rictus asked 10/9, 2008 at 14:22
4
Solved
Not sure how to even phrase the title on this one!
I have the following data:
IF OBJECT_ID ('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #data
(
id UNIQUEIDENTIFIER
,reference NVARC...
Whip asked 10/8, 2011 at 15:22
8
Solved
I have a MS SQL CTE query from which I want to create a temporary table. I am not sure how to do it as it gives an Invalid Object name error.
Below is the whole query for reference
SELECT * INTO ...
Laid asked 15/7, 2012 at 10:45
3
I have a table with hierarchical data:
This is a sample of data with id, parent id, name, code (which is sometimes not filled), level and isroot column. In real scenario there will be more level...
Osullivan asked 5/12, 2017 at 18:6
1
Solved
As a follow-up to this question:
How to "PERFORM" CTE queries in PL/pgSQL?
I try:
perform (with test_as_cte as(select * from myTable) select * from test_as_cte);
But get the following ...
Myrica asked 8/2, 2022 at 21:2
1 Next >
© 2022 - 2024 — McMap. All rights reserved.