February 10, 2022 at 11:05 am
hi,
I have used "with cte" to do some recursion, which i have used on top,
is there any why that i can do recursion with "with cte" or with other techniques in side the exists clause or in outer apply or cross apply .
note : "with out using function" as some one said function stuck sometimes.
IF OBJECT_ID (N'TEMPDB.dbo.#b', N'U') IS NOT NULL
DROP TABLE dbo.#b;
IF OBJECT_ID (N'TEMPDB.dbo.#t', N'U') IS NOT NULL
DROP TABLE dbo.#t;
Create table #b (bid bigint, parentid bigint)
insert into #b values(1, null) , (2,1),(3,2)
;
WITH PipeHierarchy(bid,parentid) AS
(
SELECT bid,parentid
FROM #b
WHERE bid IN (1)
UNION ALL
SELECT e.bid,e.parentid
FROM #b AS E
INNER JOIN PipeHierarchy AS d
ON E.parentid = d.bid
)
SELECT INTO #t FROM PipeHierarchy
select from #b b
where exists (select * from #t t where t.bid = b.bid)
February 10, 2022 at 11:27 am
Please format your code in future.
As you have not included desired results, I have no idea what you are trying to achieve. But this query runs, at least.
DROP TABLE IF EXISTS #b;
DROP TABLE IF EXISTS #t;
CREATE TABLE #b
(
bid BIGINT NOT NULL
,parentid BIGINT NULL
);
INSERT #b
(
bid
,parentid
)
VALUES
(1, NULL)
,(2, 1)
,(3, 2);
WITH PipeHierarchy (bid, parentid)
AS (SELECT bid
,parentid
FROM #b
WHERE bid = 1
UNION ALL
SELECT E.bid
,E.parentid
FROM #b E
INNER JOIN PipeHierarchy d
ON E.parentid = d.bid)
SELECT *
FROM #b b
WHERE EXISTS
(
SELECT * FROM PipeHierarchy t WHERE t.bid = b.bid
);
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 11, 2022 at 9:27 am
i know this one.
the situation is little bit different.
so the query is can we use "with cte" or recursion inside following clauses like exists or cross apply or outer apply or etc?
yours sincerely
February 11, 2022 at 10:13 am
As the statement preceding WITH in the definition of a CTE must be terminated by a semicolon, it cannot follow EXISTS or APPLY.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 11, 2022 at 7:58 pm
i know this one.
the situation is little bit different.
so the query is can we use "with cte" or recursion inside following clauses like exists or cross apply or outer apply or etc?
yours sincerely
You would have to have the CTE (WITH part) at the top of the query and reference it in the EXISTS
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply