Try this: ;WITH items AS ( SELECT EstimateItemID, ItemType , 0 AS Level , CAST(EstimateItemID AS VARCHAR(255)) AS Path FROM EstimateItem WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID UNION ALL SELECT i.EstimateItemID, i.ItemType , Level + 1 , CAST(Path + ‘.’ + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255)) FROM EstimateItem i INNER JOIN items itms … Read more
To avoid ambiguity because WITH can be used elsewhere ..FROM..WITH (NOLOCK).. RESTORE..WITH MOVE.. It’s optional to terminate statements with ; in SQL Server Put together, the previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don’t know what is before the CTE So DECLARE @foo int; WITH … Read more
If you are trying to union multiple CTEs, then you need to declare the CTEs first and then use them: With Clients As ( Select Client_No From dbo.Decision_Data Group By Client_No Having Count(*) = 1 ) , CTE2 As ( Select Client_No From dbo.Decision_Data Group By Client_No Having Count(*) = 2 ) Select Count(*) From … Read more
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
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
Updates you make to the CTE will be cascaded to the source table. I have had to guess at your schema slightly, but something like this should work. ;WITH T AS ( SELECT InvoiceNumber, DocTotal, SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal FROM PEDI_InvoiceDetail ) UPDATE T SET DocTotal = NewDocTotal
I think it should be something like: WITH cte1 as (SELECT * from cdr.Location), cte2 as (SELECT * from cdr.Location) select * from cte1 union select * from cte2 Basically, WITH is just a clause here, and like the other clauses that take lists, “,” is the appropriate delimiter.
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
Use data-modifying CTEs: WITH ins1 AS ( INSERT INTO sample(firstname, lastname) VALUES (‘fai55’, ‘shaggk’) — ON CONFLICT DO NOTHING — optional addition in Postgres 9.5+ RETURNING id AS sample_id ) , ins2 AS ( INSERT INTO sample1 (sample_id, adddetails) SELECT sample_id, ‘ss’ FROM ins1 RETURNING user_id ) INSERT INTO sample2 (user_id, value) SELECT user_id, ‘ss2’ … Read more
Update: MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs. Here’s a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/ Below is my earlier answer, which I originally wrote in 2008. MySQL 5.x does not support queries using the WITH syntax defined in SQL-99, also called Common Table Expressions. This has been a feature … Read more