So I have a few tables that I have to query to get a bunch of information. The problem I seem to be having is that there is only 1 thing joining them together and because some of them have a many-to-...
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in ...
I have several tables in postgres that we are migrating to snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following. Here is the query...
WITH CTE1 ( COL1, COL2)
I need to build vector(s) of time series dates (say 10) for a subset of start dates.
The following statement returns 10 consecutive dates starting from '2010-01-01'.
;with cte as
(select 1 i union ...
I have created an involved CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the ...
I have a table having records as below:
ID Name parentId
1 A Null
2 B 1
3 C Null
4 D 3
5 E 4
I need out put in following format
ID Name ParentName
This is SQL Server CTE,
;with grp (Sdate,TransactionType,tot)
(select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type],
(count ([Transaction_type])) as [Total Count]
I have written a view which is using a with clause having a function something like this:
create or REPLACE view abc as
with function calculator (m number, r number) return number is begin
Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well.
I had originally posted this in SO but reasoned that it would be more appropriate here.
I have a view in an sqlite database such as this:
I wrote a cte against an Oracle database and the query executes without issue when working in Oracle SQL Developer. I need to use ssis to transport the results of that query to a SQL Server database.
For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
Do unused CTEs in queries affect performance and / or alter the generated query plan?
The query looks something like this
; with stats as (
select * from openquery ([remote-server], 'select empid, stat_dt' from [statistics]') -- when run solo, returns 20K rows in 45 seconds
I tried to document myself as much as I could on the topic before coming here to bother you, but here I am anyway.
We want to implement keyset pagination on this table:
create table api.subscription ...
I have a table which contains this data:
- ID - PRIMARY KEY, INTEGER
- PC_ID - INTEGER
- USER_ID - INTEGER
- PAYMENT_DATETIME - TIMESTAMP
- FIO - VARCHAR2(255)
When I do a SELECT I get this (example)...
I'm working with one project like rental agreement system.
There are two roles types of users (User Role)
Signer = 0
Observer = 1
Anyone can register and create a document(which will be referred as ...
I have following table:
create table osoba(
id int primary key,
Data looks like below:
id idNd nazwim
1 NULL grandpa
2 1 child1
3 1 child2
4 2 ...
I am by no means a database expert so I might be asking something dumb.
So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a ...
I currently have a relatively large MTVF returning a set of data, the code I have provided below is a small subsection of what I have in place. All but 1 part of my function is built from a Cte chain ...
Inline views allow you to select from a subquery as if it were a different table:
FROM /* Selecting from a query instead of table */
I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table.
If the cust_id='11' then I need to get the ...
This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the ...
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:
Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which ...
I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless?
SELECT /* ... */
parent = (
I have two tables on which I perform some bulk inserts:
key: key_id (pk), key_name
related_key: related_key_id (pk), key_id (fk)
values_data = "($$key_1$$), ($$key_2$$)"
INSERT INTO ...
I am trying to update the single table with 4 columns (run_id, fqcn, diff_value, cleared_run_id) with the following snippet:
WITH cte AS (
SELECT diff.fqcn, diff.run_id, diff.cleared_run_id, ...
I'm getting the following error :
Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int.
I have tried casting the problematic field which is in my third CTE(CTE4)...
I am trying to figure out if there is a way I can make nested CTEs work for this particular case.
Consider the following (highly contrived) scenario which is based on the actual application: there's ...
This question is about attempting to model/design an elementary graph-like database within postgres.
The starting point is that graphs have:
Nodes have properties
Node are connected by (bi/...
I need to calculate a value by using it's previous value * ColumnX
Here is the formula:
DividendReinvested = "Previous DividendReinvested" * (1+ValuationPercChange/100)
Can I do this without a loop?...
WITH t1 AS (SELECT a.id,
FROM accounts a
I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below.
In PostgreSQL 9.5, given a simple table created with:
create table tbl (
id serial primary key,
I run SQL to INSERT a value, then UPDATE it in the same statement:
WITH newval ...
I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by ...
I'm currently transitioning a SQL backend from SQLite to CockroachDB, the latter being a semi-PGSQL-compatible database.
I have multiple tables which at their core are adjacency lists, i.e.:
I have this code:
insert into [dbo].[NGC_Agent_Intervals]
[Hour] = DATEPART(HOUR, RSRange),
hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(...
There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
I am working in PostgreSQL 9.5 on a SQL query that pulls from two views. Individually, the views take 93 msec and 530 msec to perform, resulting in ~1500 and ~6200 rows, respectively. This seems ...
I am a little stumped with this CTE update stmt:
DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);
I need to create a temp function,
for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables).
Is there any way that I could use a CTE or a #temp query ...
I have two tables
ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
I was able to convert a Merge which has one condition in both MATCHED & UNMATCHED clauses using CTE. But not sure how to do (both DELETE & UPDATE), if I have two conditions in the same clause.
We have a SQL Server database which has a database audit specification which audits all execute actions on the database.
CREATE DATABASE AUDIT SPECIFICATION [dbAudit]
FOR SERVER AUDIT [servAudit]
Is there a way to execute multiple operations using the WITH statement?
WITH T AS
SELECT * FROM Tbl
OPEN P_OUTCURSOR FOR
SELECT * FROM T;
SELECT COUNT(*) INTO ...
I use the following query on SQL Server 2012 Express to move data that is older than 4 days old out of one [primary] database and into another [secondary database] for archiving purposes. The only ...
Given the below CTE:
with seekResults as