Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
19 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
1
vote
1answer
33 views

Recursive Parent-Child Location Relationship Cache as View

Here is my data structure: CREATE TABLE Locations( LocID int IDENTITY(1,1) NOT NULL, LocationTypeID int NOT NULL, ParentID int NULL, LocNum varchar(50) NULL, LocName varchar(250) ...
0
votes
2answers
44 views

Want to delete then insert with single statement using a CTE in Postgres

I want to delete then insert with single statement using a CTE in Postgres. We could use an explicit transaction and 2 statements, but want to avoid that if possible. I don't know if there will be ...
0
votes
1answer
37 views

List Top Daily Rentals Per Customer using a CTE

I've been trying to learn CTEs and having a bit or trouble getting the hang of them. I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. ...
0
votes
2answers
16 views

mysql - insert multiple values sharing same foreign key

I have the following tables: create table countries ( country_id tinyint unsigned, iso_alpha3_code varchar(4) ); create table provinces ( province_id bigint unsigned, province_name ...
2
votes
1answer
34 views

Select rows based on values from 1 column being the same?

An example of my database table film id | scene id | value id ----------------------------- 0 | 0 | 7 0 | 1 | 1 0 | 1 | 0 1 | 2 | 8 ...
3
votes
1answer
63 views

Get the total count of every child foreign keys

I have been trying this in SQL EXPRESS 2016. Here is my problem: I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table. ...
1
vote
0answers
9 views

Propagate where condition into CTE of a view, using index?

I have written a multi-step view using CTEs. The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that ...
1
vote
1answer
37 views

How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp ...
0
votes
0answers
19 views

Recursive sum of children with calculated column

I'm trying to create a recursive query to determine the total cost of a multi-level bill of materials (kind of like a baking recipe list, that has sub-recipes). The top level bill of materials could ...
0
votes
2answers
36 views

how to view data as history of field value change

Example i have data like id employeeId Salary Year 1 1 12000 2001 1 2 10000 2001 1 1 16000 2002 1 3 12000 2004 1 ...
1
vote
0answers
23 views

data-modifying CTEs and triggers

If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run? -- t1 and t2 both have insert ...
0
votes
1answer
23 views

SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should

CTE SQLFiddle I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from ...
2
votes
2answers
282 views

WITH … AS statement not working as expected

I get this error: It arises when I try to execute the following query: with expensive_service as ( select s1.* from service s1, service s2 where s1.price > s2.price ) select * from ...
2
votes
2answers
54 views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
1
vote
0answers
36 views

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that ...
0
votes
0answers
21 views

Splitting a large table using CTE with a constraint

I have a large table called "order". That table has a field record_type. I want to split the table up into different orders by record type. All of the tables for the different types of orders have the ...
1
vote
1answer
31 views

SQL Conditional Join - remove duplication from Stored Procedure

This stored procedure accepts a search parameter. I want to join on a table valued function if the search parameter is not null. The way I have written it works, but it seems like lots of ...
5
votes
1answer
902 views

Why am I getting nondeterministic results with the deterministic function STDEV()?

This is type of query I'm trying to run: WITH CTE_Ordered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId FROM SourceTable ), ...
4
votes
1answer
224 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
41 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 ...
3
votes
1answer
305 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
26 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
62 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 ...
7
votes
2answers
472 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
37 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
34 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
26 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
71 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
72 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
31 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
25 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
30 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
109 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
179 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
136 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
76 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
650 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
124 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
47 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
38 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
32 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
73 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
64 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
41 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
983 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
113 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 ( ...

1
2 3 4 5 6