common-table-expression

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

by

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

Are SELECT type queries the only type that can be nested?

by

Basic answer There are CTEs (Common Table Expressions) in Postgres – like in any major modern RDBMS, That includes MySQL since version 8.0. Since version 9.1 Postgres also features data-modifying CTEs, which can be “nested”. Unlike subqueries CTEs pose as optimization barriers. The query planner cannot inline trivial commands into the main command or reorder … Read more

Window Functions or Common Table Expressions: count previous rows within range

by

I don’t think you can do this cheaply with a plain query, CTEs and window functions – their frame definition is static, but you need a dynamic frame (depending on column values). Generally, you’ll have to define lower and upper bound of your window carefully: The following queries exclude the current row and include the … Read more

CTE error: “Types don’t match between the anchor and the recursive part”

by

Exactly what it says: ‘name1’ has a different data type to ‘name’ + CAST((rn+1) as varchar(255)) Try this (untested) ;with cte as ( select 1 as rn, CAST(‘name1’ as varchar(259)) as nm union all select rn+1,nm = ‘name’ + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte Basically, you have to … Read more

Delete parent if it’s not referenced by any other child

by

In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations: WITH del_child AS ( DELETE FROM child WHERE child_id = … Read more