Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
1
vote
0answers
24 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
-2
votes
1answer
32 views

Common Table Expressions [closed]

Are there alternatives to common table expressions in Oracle 12 c? I'v been using the syntax from SQL Server and oracle gives me the following error. SELECT CTE_TEST AS (SELECT DISTINCT deptno, ename ...
1
vote
0answers
114 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) ...
0
votes
0answers
23 views

How to CALL Procedure inside WITH clause and use RETURNING in PROCEDURE CALL

I have query as shown below. I get the id from one table after inserting value in it,the id in "table1" is set as SERIAL I want the id from another table which is "table2", I use a Procedure to ...
0
votes
1answer
24 views

Is it possible to bulk insert using CTE in MySQL?

I created this small sample fiddle to play with the model: https://www.db-fiddle.com/f/b37ASVuSxna9wEoWn9hZDw/0 I have a table called folder, which represents a folder hierarchy with a parent_id ...
5
votes
1answer
183 views

After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL ...
0
votes
1answer
27 views

How to use a recursive CTE to get ancestors in a hierarchy

create table division ( id serial primary key, name varchar not null ); -- nested sets table create table location ( id serial primary key, name varchar not null, division_id integer not ...
1
vote
1answer
33 views

Table-valued Data in Common Table Expression

Most other databases allow you to generate table values using VALUES. As far as I can tell, SQL Server lets you do this with subqueries: SELECT * FROM ( VALUES ('Cornelius','Eversoe', '...
-1
votes
1answer
25 views

MS SQL 2017: hierarchy tree - get quantity of last level components

There's a table of items with their components. Components depth vary i.e. some items may have one level of components other may have tens nested components. I need to get quantinty of last level ...
3
votes
2answers
33 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
3
votes
1answer
65 views

Grouping values into buckets

I'm working a query and I wasn't sure if my current approach is the "best" approach. I got a table filled with values and a table with buckets that I use to group those values. There are 2 types of ...
0
votes
0answers
39 views

Order By not working correctly in common table expression in Fetch clause while retrieving data

I have been working on a stored procedure optimization which fetches data from multiple tables with many left joins. The problem I am facing is that in common table expression while fetching the ...
0
votes
2answers
70 views

all the dependants of a stored procedure or function recursively - but shown only once

I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases. so I have ...
0
votes
1answer
30 views

Recursion only if a criteria is met

I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow : CREATE TABLE PARTLIST (PART VARCHAR(16), SUBPART VARCHAR(16), QUANTITY INT, ...
-1
votes
1answer
24 views

I want to transpose Column values into Row of Specific values like 'GType'(values as rows) as mentioned in the below example

Example Table: Result Table: I want to transpose Column into Rows of Specific Row values like 'GType'(values) as mentioned in the below example. I want a results to be in the below example format. ...
-1
votes
1answer
28 views

Product part Query (BOM) [closed]

I'm using DB2 V7 and i have a product table like below : select * from product; Product |Component|Quantity |Line number| --------+---------+---------+------------ Product1|Part1 |1 |1 ...
0
votes
2answers
105 views

New to CTE - How to do a count after making table unique

I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries. I have the below table in SQL. I'm trying to write ...
1
vote
1answer
84 views

Can I put a CTE inside a cross join?

I have a main query that have a bunch of cross joins in it such as: SELECT val1, val2, ... FROM ( SELECT top 1 id as val1 FROM TableA ) A CROSS JOIN ( SELECT top 1 id as val2 FROM ...
0
votes
1answer
33 views

How to have CTE query recurse parent query result one by one?

I have this query used on Postgres to return place administrative levels. WITH RECURSIVE hierarchy(name, pinyin, level) AS ( SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk from ...
-1
votes
1answer
56 views

with clause sql statement maximum limit in oracle 12c [closed]

with s as (select distinct s.empno, s.deptnumber, s.age, s.salary from emp.employee details s ) ...
-1
votes
1answer
68 views

Is there any possible way to modify the database state from within the scope of a CTE? [closed]

For example: -- Directly with a DML Statement WITH CTE_DML AS ( UPDATE Table1 SET Column1 = 'Value1' FROM Table1 ) -- Indirectly by executing a procedure WITH CTE_SP AS ( EXEC ...
8
votes
2answers
450 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
3
votes
2answers
117 views

Can subqueries produce different execution plans than CTE's?

I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test: ...
0
votes
1answer
31 views

How to insert values to one table from the table stored in another database in postgresql?

Here is the structure of table: `* Table 1 of database A contains mobile number and userId and both field cannot be blank I want to fetch data from table 1 of database A and insert that with some ...
2
votes
1answer
36 views

Why does DELETE not work after an UPDATE in a CTE?

CREATE table test ( id integer, x integer ); INSERT INTO test VALUES (1, 1); SELECT works: WITH res AS ( UPDATE test SET x = 10 WHERE id = 1 RETURNING id ) SELECT id FROM test WHERE ...
-1
votes
1answer
31 views

Advantage of 'with' queries over plain 'select' and joins?

Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise? I am asking solely about reading data (from catalogues specifically).
3
votes
1answer
69 views

Reused Nested Decision Logic - CTE vs Copying Code

Question I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement. If I ...
1
vote
1answer
57 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
0
votes
1answer
31 views

Reusing literal string in different parts of query

I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings: with cte as ( select 'The character between the xes is evil: xx' as ...
2
votes
1answer
463 views

UPDATE table using CTE

I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works: WITH cte AS ( SELECT AllTimeRank, r = RANK () OVER (PARTITION BY ...
1
vote
3answers
112 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
0
votes
0answers
14 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...
0
votes
1answer
45 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: ...
0
votes
2answers
401 views

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming. SQL SERVER Command: CREATE VIEW [dbo].[vw_PurchParent] as WITH cte AS ( SELECT a._Id,...
1
vote
1answer
67 views

Overlaps and Gap grouping in SQL Server

I am trying to find gaps in my data and then group those. The table I am working with looks like this: runid|year |road|start |end 01010| 9 |2 |0.000 |0.585 01100| 9 |2 |0.585 ...
2
votes
1answer
159 views

CTE Query to CONCAT text (over 10,000 rows)

I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber. Below is what I am working with: CREATE TABLE #SampleTable ( ...
0
votes
0answers
45 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
0
votes
1answer
144 views

Unable to wrap a simple CTE in plpgsql loop

For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make ...
2
votes
1answer
90 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
0
votes
0answers
56 views

Fulltext search in a CTE result - missing fulltext index

My question applies to MariaDB 10.2.7 The query WITH prefiltered AS ( SELECT id, fulltext_indexed_column FROM fulltext_table WHERE id < 30 ) SELECT * FROM prefiltered WHERE MATCH(...
0
votes
1answer
92 views

How to make use of WITH in PostgreSQL to reduce query cost

I am trying to get twitter data of narendramodi using below command. SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
0
votes
1answer
63 views

Select All Instances of Substring in a String

I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId ...
3
votes
3answers
892 views

SQL Server CTE Bottom to Top Recursive with Where clause

I have an Employee Table with an EmployeeId, ManagerId and a Name field. The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null). I found ...
0
votes
1answer
204 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
3
votes
2answers
341 views

Postgres CTE optimization with nested json_build_object

I'm trying to write a query that returns data from multiple tables and aggregates it in one nested JSON field. I feel like this would perform great on SqlServer but, as Brent Ozar wrote in this post, ...
1
vote
1answer
145 views

Rewrite SQL query in CTE query

I have this table which I would like to store a chain of records. CREATE TABLE table_name ( id INT, unique_id varchar, reference_id varchar, ); I want to implement SQL query for MariDB ...
1
vote
0answers
83 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
0
votes
0answers
365 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
19
votes
4answers
3k views

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
1
vote
0answers
30 views

Guarding against CTE infinite loops in JOIN condition considered bad practice?

In docs, and pretty much everywhere else on the net, to guard against loops in recursive CTEs people use cycle column. The problem with this solution (as opposed to doing the ANY(path) check in join ...