common-table-expression - w3toppers.com

Insert into from CTE

This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears. Your options include: Redefine the CTE a second time. This is as simple as copy-paste from WITH… through the end of the definition to before your SET. Put your results into a #temp table or a @table … Read more

Count consecutive duplicate values in SQL

I am going to presume that id is unique and increasing. You can get counts of consecutive values by using the different of row numbers. The following counts all sequences: select grp, value, min(id), max(id), count(*) as cnt from (select t.*, (row_number() over (order by id) – row_number() over (partition by value order by id) … Read more

Store and reuse value returned by INSERT … RETURNING

… that can be used to insert values into other tables? You can even do that in a single SQL statement using a data-modifying CTE: WITH ins1 AS ( INSERT INTO tbl1(txt) VALUES (‘foo’) RETURNING tbl1_id ) INSERT INTO tbl2(tbl1_id) SELECT * FROM ins1 Requires PostgreSQL 9.1 or later. db<>fiddle here (Postgres 13) Old sqlfiddle … Read more

PostgreSQL: Query has no destination for result data

The stored procedure won’t just return the result of the last SELECT. You need to actually return the value: CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$ BEGIN — …. RETURN(SELECT dblink_disconnect()); END $$ LANGUAGE plpgsql; You’re getting the error because Postgres expects the function to return something of type text, but your function … Read more

Select statement to return parent and infinite children

So referencing this answer: SQL Server CTE Parent Child recursive Here’s a working version with your schema: Table Creation Script CREATE TABLE YOUR_TABLE ([ID] int, [ParentID] int, [Name] varchar(21)) ; INSERT INTO YOUR_TABLE ([ID], [ParentID], [Name]) VALUES (1, NULL, ‘A root’), (2, NULL, ‘Another root’), (3, 1, ‘Child of 1’), (4, 3, ‘Grandchild of 1’), … Read more

Syntax error: WITH is not valid input in this position

WITH customers_in_usa AS is for now invalid MySQL code. MySQL will support CTE’s in the future in MySQL version 8.. You could rewrite your SQL code, that should give the same results. SELECT customerName , state FROM customers WHERE country = ‘USA’ AND state=”CA” ORDER BY customerName

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Sample DDL create table #Temp ( EventID int, EventTitle Varchar(50), EventStartDate DateTime, EventEndDate DatetIme, EventEnumDays int, EventStartTime Datetime, EventEndTime DateTime, EventRecurring Bit, EventType int ) ;WITH Calendar AS (SELECT /*…*/) Insert Into #Temp Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle … Read more

How to set the maxrecursion option for a CTE inside a Table-Valued-Function

From this MSDN forums thread I learn that [the] OPTION clause can be used only at the statement level So you cannot use it within a query expression inside view definitions or inline TVFs etc. The only way to use it in your case is to create the TVF without the OPTION clause and specify … Read more

How to use multiple CTEs in a single SQL query?

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive: WITH RECURSIVE cte1 AS (…) — can still be non-recursive , cte2 AS (SELECT … UNION … Read more