An acronym for Common Table Expression. They are temporary, reusable subqueries that may be recursive.

learn more… | top users | synonyms

2
votes
3answers
179 views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId ...
1
vote
2answers
38 views

Join Two queries with different declarations

I need to join two different queries to get some columns from both. The first one has a CTE. Query1: WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, ...
5
votes
1answer
57 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
-1
votes
1answer
66 views

CTE Expression not working

I am trying to get the employee hierarchy as output with empLevel indicating the reporting layer. However, my query returns only one row i.e. The CEO of the company while it's supposed to return all ...
2
votes
2answers
96 views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other ...
2
votes
1answer
62 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
1
vote
1answer
85 views

Query for hierarchical RBAC scenario

I have a SQLite database with a hierarchical role-based access control (RBAC) data model. In this model, users are granted permissions to perform certain actions against various resources either ...
0
votes
1answer
47 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
1
vote
1answer
117 views

Using a CTE in IF EXISTS Query

Is it possible to do something similar to the following in SQL Server 2012? IF EXISTS ( WITH DATA AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn ...
-1
votes
1answer
91 views

Group by function not working correctly in SQL server CTE [closed]

This is SQL Server CTE, ;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] ...
1
vote
0answers
126 views

Most efficient way to bulk insert or upsert data into multiple related tables

Given a PostgreSQL 9.4 schema with 3 related tables: CREATE TABLE a ( id BIGSERIAL PRIMARY KEY, a_name VARCHAR(80), a_field INTEGER ); CREATE TABLE b ( id BIGSERIAL PRIMARY KEY, b_name ...
6
votes
3answers
378 views

Check if any of the values is in a subquery result

I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
0
votes
0answers
59 views

Optimizing a CTE query - overhead in inserting into a new table from select statement

I have two tables with the same structure like so (let us call them initial_table1 and initial_table2): tempid name num_records 1 a 2 2 b 2 3 c 2 4 d ...
2
votes
1answer
197 views

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master ...
0
votes
2answers
75 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
0
votes
0answers
44 views

What is the difference between normal Select query and a Select query inside Common Table Expression(CTE)? [duplicate]

I'm having a table 'Products' and if Execute this query SELECT TOP 1000 [ProductID] ,[ProductDesc] ,[ManufacturingDate] ,[ExpiryDate] ,[Price] FROM ...
1
vote
2answers
199 views

join table or cte

First off, I've tried to find a suitable solution on the internet, but I really didn't find one that worked for me. What seems to be my problem? Well... At my new job we use MySQL. I'm used to ...
0
votes
1answer
73 views

Parent-child Relationship with data scattered between these records

I have a 60 column query that fills in a card on an application. The problem I am running into is gathering all the parent-child relationship data into one record per child. As a quick example: The ...
1
vote
1answer
42 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT ...
1
vote
1answer
169 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
0
votes
1answer
89 views

Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue... if i run my CTE code ...
0
votes
0answers
73 views

SQLite 3 Tree List Without CTE

I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite ...
0
votes
0answers
39 views

How to determine is a value exists in a column within a partition

I have a set of data where I need to look at partitioned data and determine if a value exists within that partition. Example data: AccountNumber Code Status Credited ...
1
vote
1answer
59 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
1
vote
1answer
73 views

Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]

I have the following query: SELECT COUNT(*) AS `count` FROM agreements WHERE start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y') AND start_date < ...
2
votes
0answers
64 views

Can CTE sub-statements be prevented from executing at all?

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary. The reason why is that upon ...
3
votes
1answer
149 views

Are CTEs multithreaded?

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...
2
votes
1answer
184 views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
5
votes
2answers
238 views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...
1
vote
1answer
211 views

T-SQL View — How to 'pre-fetch' schema using scalar function, then populate using table query

I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to ...
4
votes
2answers
504 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
1
vote
2answers
87 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
1
vote
2answers
121 views

WITH Clause UNION Issue

WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice ...
6
votes
1answer
1k views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
0
votes
1answer
337 views

sql or hql query to find all parents of a particular record

I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1. any help would ...
0
votes
1answer
131 views

CTE returns an empty set even though anchor is non-empty

I'm got an adjacency list consisting of two tables: CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY); CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id ...
1
vote
1answer
136 views

Query hint? for deduplicated CTE in Merge

Referring to this link: Optimizing MERGE Statement Performance The following criteria are given for ideal performance: Create an index on the join columns in the source table that is unique and ...
1
vote
1answer
447 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
2
votes
0answers
110 views

Why does Recursive CTE estimate just 1 row?

Given two cascading, self-contained (no real tables) recursive CTE's: create view NumberSequence_0_100_View as with NumberSequence as ( select 0 as Number union all select Number + 1 ...
0
votes
1answer
77 views

ERROR: recursive reference to query “najdi_tot” must not appear within its non-recursive term

I have a trouble with the following CTE query: WITH RECURSIVE najdi_tot(sj) AS ( SELECT nad FROM s_vztah WHERE pod=581 UNION SELECT pod FROM s_vztah WHERE nad=581 UNION SELECT ...
2
votes
1answer
94 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
3
votes
1answer
175 views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
2
votes
1answer
484 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
1
vote
0answers
633 views

How to improve query performance in PostgreSQL? [closed]

I have the following query to be execute within a second but getting time for execute 2.8 minutes: Query: Using Common Table Expression. explain analyze with temp_cte as ( Select ...
-1
votes
1answer
634 views

combining two CTE in a single query

Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select ...
9
votes
1answer
358 views

How do I 'hint' the cardinality of a recursive CTE?

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs: with recursive w(n) as ( select 1 union all ...
1
vote
0answers
1k views

Calculate the compound interest with variable annual rate

I'm moving this question from SO Some years ago I was asked to create something to calculate the current value of a lot of insurance policies, I came out with a query that worked fine, but I was ...
2
votes
1answer
1k views

CTE Error (nvarchar to numeric)

I'm using a CTE to convert SSRS stored proc's into BO stored proc's, as apparently I can't use temp tables with Business Objects. I'm having this query: ;WITH cte1 AS ( SELECT cv.issue ...
3
votes
1answer
1k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
3
votes
1answer
992 views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...