Need Help With CTEs
Mar 3, 2008
I am trying to use CTEs so as to be able to reuse the results for subsequent queries. But I am having trouble with the syntaxes for CTEs and could do with some help here
Basically, this is how my final query would look like.
Code SnippetWITH T1 AS (SELECT id, parent_id, end_desc_id FROM GO WHERE (parent_id = - 1))
WITH T2 AS (SELECT g.id, g.parent_id, g.end_desc_id FROM GO AS g, T1 AS t WHERE g.parent_id = t.id)
SELECT G.* FROM GO G, T2 as T WHERE G.id>=T.id AND G.id<=T.end_desc_id ORDER BY G.id, T.id
GO is defined as (id, parent_id, end_desc_id, path, value)
TIA
View 5 Replies
Jul 29, 2015
I am currently using 5 separate CTEs and I am wondering if I can use only 1 cte. What I am listing below is what 2 ctes looks like. Thus can you tell me if I can use the same cte that is listed in the sql below? If so, how to change the sql listed below to use only 1 cte?
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo2
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[code]....
View 6 Replies
View Related
Sep 9, 2007
Hi All,
I faced this issue regarding the SQL Server 2005 CTEs
Hope to get an explanation
here is the code,
Code Snippetwith MyCTE(i)
as
(
select i = 1
union all
select i = i + 1 from MyCTE where i < 10
)
select i from MyCTE
order by i
this will display the numbers from 10 rows containing values from 1 to 10, although the where clause is so clear i<10
if we replace it with i<=10, it will display 11 rows from 1 to 11
So as we know the recursion process here is limited by default to 100 (we can change it using option (maxrecursion n) ),this can yield that it is not limited to 100 but 101 even u changed the limit to 1000 it will not be 1000 but 1001, but this may be correct as the recursion counting is began after union all so it should be 100 and display 101 in the result set but my question is clear above (the max value displayed and the where clause)
Thanks in advance
----------
ٌٌRegards,
Anwer Matter
View 2 Replies
View Related
May 13, 2014
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as (
select 0 [iterations], 1e+0 [computed]
union all
select iterations+1,dbo.f(computed)
from where rCTE
where iterations < 30
)
select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
View 4 Replies
View Related
