Recursive CTE in presence of circular references
You will need to put your WHERE-filter in the CTE part of your query, like so:
WITH recursive_cte (root_id, id) AS (
SELECT parent_id, id
FROM test_cte
WHERE id=0 -- Restrict your recursion to start from the item with id = 0, instead of considdering all items.
UNION ALL
SELECT t.parent_id, r.id
FROM test_cte t
INNER JOIN recursive_cte r
ON (r.root_id=t.id)
)
SELECT *
FROM recursive_cte
TSQL CTE: How to avoid circular traversal?
You need to accumulate a sentinel string within your recursion. In the following example I have a circular relationship from A,B,C,D, and then back to A, and I avoid a loop with the sentinel string:
DECLARE @MyTable TABLE(Parent CHAR(1), Child CHAR(1));
INSERT @MyTable VALUES('A', 'B');
INSERT @MyTable VALUES('B', 'C');
INSERT @MyTable VALUES('C', 'D');
INSERT @MyTable VALUES('D', 'A');
; WITH CTE (Parent, Child, Sentinel) AS (
SELECT Parent, Child, Sentinel = CAST(Parent AS VARCHAR(MAX))
FROM @MyTable
WHERE Parent = 'A'
UNION ALL
SELECT CTE.Child, t.Child, Sentinel + '|' + CTE.Child
FROM CTE
JOIN @MyTable t ON t.Parent = CTE.Child
WHERE CHARINDEX(CTE.Child,Sentinel)=0
)
SELECT * FROM CTE;
Result:
Parent Child Sentinel
------ ----- --------
A B A
B C A|B
C D A|B|C
D A A|B|C|D
Detecting circular references in SQL
To check for circular references i have used a trigger and recursive CTE:
CREATE TRIGGER trgIU_X_CheckCircularReferences
ON dbo.X
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Results TABLE ([Exists] BIT);
WITH CteHierarchy
AS
(
SELECT x.A, x.B, X.C, 1 AS [Type]
FROM inserted i
JOIN X x ON i.A = x.A AND i.C = x.B
UNION ALL
SELECT x.A, x.B, X.C, 2 AS [Type]
FROM CteHierarchy i
JOIN X x ON i.A = x.A AND i.C = x.B
WHERE NOT EXISTS
(
SELECT *
FROM inserted a
WHERE a.A = x.A AND a.B = x.B
)
)
INSERT @Results ([Exists])
SELECT TOP(1) 1
FROM CteHierarchy h
JOIN X x ON h.A = x.A AND h.C = x.B
OPTION(MAXRECURSION 1000);
IF EXISTS(SELECT * FROM @Results)
BEGIN
ROLLBACK;
RAISERROR('Circular references detected', 16, 1);
END
END
GO
Now, we can run some tests:
--Test 1 - OK
PRINT '*****Test 1 - OK*****';
SELECT * FROM X;
BEGIN TRANSACTION;
UPDATE X
SET C = 'B1'
WHERE B = 'B4';
SELECT * FROM X;
--This transaction can be commited without problems
--but I will cancel all modification so we can run the second test
ROLLBACK TRANSACTION;
PRINT '*****End of test 1*****';
GO
--Test 2 - NOT OK
PRINT '*****Test 2 - NOT OK*****';
SELECT * FROM X;
BEGIN TRANSACTION;
UPDATE X
SET C = 'B1'
WHERE B = 'B1';
--Useless in this case (test 2 & test 3)
--Read section [If a ROLLBACK TRANSACTION is issued in a trigger] from http://msdn.microsoft.com/en-us/library/ms181299.aspx
SELECT * FROM X;
--Useless
ROLLBACK TRANSACTION;
--Useless
PRINT '*****End of test 2*****';
GO
PRINT '*****Test 3 - NOT OK*****';
SELECT * FROM X;
BEGIN TRANSACTION;
UPDATE X
SET C = 'B4'
WHERE B = 'B1';
GO
Results:
*****Test 1 - OK*****
(4 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
*****End of test 1*****
*****Test 2 - NOT OK*****
(4 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure trgIU_X_CheckCircularReferences, Line 34
Circular references detected
Msg 3609, Level 16, State 1, Line 8
The transaction ended in the trigger. The batch has been aborted.
*****Test 3 - NOT OK*****
(4 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure trgIU_X_CheckCircularReferences, Line 34
Circular references detected
Msg 3609, Level 16, State 1, Line 7
The transaction ended in the trigger. The batch has been aborted.
For the second test, you can see how this trigger has canceled (ROLLBACK TRANSACTION) the transaction and, after UPDATE, nothing has been executed (in current batch).
Recursive CTE to populate latest available data across many quarters?
One solution is to get from the UholdingsPE table all combinations of all quarters between the minimum and maximum values of the Reference_Date column and all the different values of the Investment and Holding columns. All the rows are returned from the cih CTE. After that, you need to extract the value of the Price column of the row of the UholdingsPE table that is the closest by of the value of the Reference_Date column to the value of the Reference_Date column of the current row of the cih CTE.
with
cc as (
select
min(Reference_Date) as Reference_Date,
max(Reference_Date) as xq
from UholdingsPE
union all
select eomonth(dateadd(qq, 1, q)), xq
from cc
where q < xq
),
ii as (select distinct Investment from UholdingsPE),
hh as (select distinct Holding from UholdingsPE),
cih as (
select Reference_Date, Investment, Holding
from cc
cross join ii
cross join hh
)
select *
from cih
cross apply (
select a.Price
from UholdingsPE as a
where
a.Reference_Date <= cih.Reference_Date and
a.Investment = cih.Investment and
a.Holding = cih.Holding
order by a.Reference_Date desc
offset 0 rows
fetch first 1 row only
) as ca
order by Reference_Date, Investment, Holding
option (maxrecursion 0);
Output:
+----+---------------------+----------------+-----------+-------+
| | Reference_Date | Investment | Holding | Price |
+----+---------------------+----------------+-----------+-------+
| 1 | 31.03.2017 00:00:00 | Example Fund 1 | Apple | 1 |
| 2 | 31.03.2017 00:00:00 | Example Fund 1 | Microsoft | 1 |
| 3 | 30.06.2017 00:00:00 | Example Fund 1 | Apple | 1 |
| 4 | 30.06.2017 00:00:00 | Example Fund 1 | Microsoft | 1 |
| 5 | 30.09.2017 00:00:00 | Example Fund 1 | Apple | 1 |
| 6 | 30.09.2017 00:00:00 | Example Fund 1 | Microsoft | 1 |
| 7 | 31.12.2017 00:00:00 | Example Fund 1 | Apple | 1 |
| 8 | 31.12.2017 00:00:00 | Example Fund 1 | Microsoft | 1 |
| 9 | 31.03.2018 00:00:00 | Example Fund 1 | Apple | 1 |
| 10 | 31.03.2018 00:00:00 | Example Fund 1 | Microsoft | 1 |
| 11 | 30.06.2018 00:00:00 | Example Fund 1 | Apple | 22 |
| 12 | 30.06.2018 00:00:00 | Example Fund 1 | Microsoft | 22 |
| 13 | 30.09.2018 00:00:00 | Example Fund 1 | Apple | 22 |
| 14 | 30.09.2018 00:00:00 | Example Fund 1 | Microsoft | 22 |
| 15 | 31.12.2018 00:00:00 | Example Fund 1 | Apple | 33 |
| 16 | 31.12.2018 00:00:00 | Example Fund 1 | Microsoft | 33 |
| 17 | 31.03.2019 00:00:00 | Example Fund 1 | Apple | 33 |
| 18 | 31.03.2019 00:00:00 | Example Fund 1 | Microsoft | 33 |
| 19 | 30.06.2019 00:00:00 | Example Fund 1 | Apple | 44 |
| 20 | 30.06.2019 00:00:00 | Example Fund 1 | Microsoft | 44 |
| 21 | 30.09.2019 00:00:00 | Example Fund 1 | Apple | 44 |
| 22 | 30.09.2019 00:00:00 | Example Fund 1 | Microsoft | 44 |
| 23 | 31.12.2019 00:00:00 | Example Fund 1 | Apple | 55 |
| 24 | 31.12.2019 00:00:00 | Example Fund 1 | Microsoft | 55 |
+----+---------------------+----------------+-----------+-------+
Demo.
Let's try the cursor-based solution.
declare
@prev_inv varchar(14), @Investment varchar(14),
@prev_hold varchar(9), @Holding varchar(9),
@prev_ref_date date, @Reference_date date,
@prev_price integer, @Price integer, @qdiff integer;
declare
c cursor forward_only static read_only for
select Investment, Holding, Reference_Date, Price from UholdingsPE
union all
select Investment, Holding, ref_date, -1
from (select max(Reference_Date) as ref_date from UholdingsPE) as a
cross join (select distinct Investment from UholdingsPE) as b
cross join (select distinct Holding from UholdingsPE) as c
order by Investment, Holding, Reference_Date, Price desc;
open c;
fetch next from c into @Investment, @Holding, @Reference_Date, @Price;
select
@prev_inv = @Investment,
@prev_hold = @Holding,
@prev_ref_date = @Reference_date,
@prev_price = @Price;
while @@fetch_status = 0 begin
fetch next from c into @Investment, @Holding, @Reference_Date, @Price;
set @qdiff = datediff(q, @prev_ref_date, @Reference_date);
if @prev_inv = @Investment and
@prev_hold = @Holding and
@qdiff > 1
begin
insert into miss_UholdingsPE(Investment,
Holding,
Price,
Reference_Date)
select
@prev_inv, @prev_hold, @prev_price,
eomonth(dateadd(q, nums.n, @prev_ref_date))
from nums
where nums.n < @qdiff + iif(@Price = -1, 1, 0);
end;
select
@prev_inv = @Investment,
@prev_hold = @Holding,
@prev_ref_date = @Reference_date,
@prev_price = @Price;
end;
close c;
deallocate c;
Cursor-based solution demo.
Another set-based solution:
with
a as (select distinct Investment, Holding from stage_UholdingsPE),
b as (
select Q.Reference_Date, a.Investment, a.Holding
from a
cross join (
select eomonth(dateadd(quarter, n, '20160331'))
from Nums(0, datediff(quarter, '20160331', '20191231'))
) as Q(Reference_Date)
where not exists(select * from stage_UholdingsPE as s
where
s.Reference_Date = Q.Reference_Date and
s.Investment = a.Investment and
s.Holding = a.Holding)
)
insert into stage_UholdingsPE(Reference_Date, Investment, Holding, Price)
select Reference_Date, Investment, Holding, null from b;
go
with
a as (
select
*,
count(Price) over(partition by Investment, Holding
order by Reference_Date
rows unbounded preceding) as c
from stage_UholdingsPE
)
insert into full_UholdingsPE(Reference_Date, Investment, Holding, Price)
select
Reference_Date, Investment, Holding,
max(Price) over(partition by c) as Price
from a;
Demo.
How to prevent insertion of cyclic reference in SQL
Note first that it is preferable to detect cycles in another environment as recursive CTEs aren't known for their good performance and neither is a trigger that would run for each insert statement. For large graphs, a solution based on the solution below will likely be inefficient.
Suppose you create the table as follows:
CREATE TABLE dbo.lnk (
node_from INT NOT NULL,
node_to INT NOT NULL,
CONSTRAINT CHK_self_link CHECK (node_from<>node_to),
CONSTRAINT PK_lnk_node_from_node_to PRIMARY KEY(node_from,node_to)
);
That would block inserts with node_from equal to node_to, and for rows that already exist.
The following trigger should detect cyclic references by throwing an exception if a cyclic reference is detected:
CREATE TRIGGER TRG_no_circulars_on_lnk ON dbo.lnk AFTER INSERT
AS
BEGIN
DECLARE @cd INT;
WITH det_path AS (
SELECT
anchor=i.node_from,
node_to=l.node_to,
is_cycle=CASE WHEN i.node_from/*anchor*/=l.node_to THEN 1 ELSE 0 END
FROM
inserted AS i
INNER JOIN dbo.lnk AS l ON
l.node_from=i.node_to
UNION ALL
SELECT
dp.anchor,
node_to=l.node_to,
is_cycle=CASE WHEN dp.anchor=l.node_to THEN 1 ELSE 0 END
FROM
det_path AS dp
INNER JOIN dbo.lnk AS l ON
l.node_from=dp.node_to
WHERE
dp.is_cycle=0
)
SELECT TOP 1
@cd=is_cycle
FROM
det_path
WHERE
is_cycle=1
OPTION
(MAXRECURSION 0);
IF @cd IS NOT NULL
THROW 67890, 'Insert would cause cyclic reference', 1;
END
I tested this for a limited number of inserts.
INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,2); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,4); -- OK
And
INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- PK violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,1); -- Check constraint violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,2); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,1); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,1); -- Exception: Insert would cause cyclic reference
It also detects cyclic references already present in the inserted rows if inserting more than one row at once, or if a path longer than one edge would be introduced in the graph. Going off on the same initial inserts:
INSERT INTO dbo.lnk(node_from,node_to)VALUES(8,9),(9,8); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,5),(5,6),(6,1); -- Exception: Insert would cause cyclic reference
Prevent circular reference in MS-SQL table
Finally, I have created the scripts after some failures, its working fine for me.
-- To hold the Account table data
Declare @Accounts table (ID INT, ParentAccountID INT)
-- To be updated
Declare @AccountID int = 4;
Declare @ParentAccountID int = 7;
Declare @NextParentAccountID INT = @ParentAccountID
Declare @IsCircular int = 0
INSERT INTO @Accounts values (1, NULL), (2,1), (3,1) ,(4,3), (5,4), (6,5), (7,6), (8,7)
-- No circular reference value
--Select * from @Accounts
-- Request to update ParentAccountID to 7 for the Account ID 4
update @Accounts
set ParentAccountID = @ParentAccountID
where ID = @AccountID
Select * from @Accounts
WHILE(1=1)
BEGIN
-- Take the ParentAccountID for @NextParentAccountID
SELECT @NextParentAccountID = ParentAccountID from @Accounts WHERE ID = @NextParentAccountID
-- If the @NextParentAccountID is NULL, then it reaches the top level account, no circular reference hence break the loop
IF (@NextParentAccountID IS NULL)
BEGIN
BREAK;
END
-- If the @NextParentAccountID is equal to @AccountID (to which the update was done) then its creating circular reference
-- Then set the @IsCircular to 1 and break the loop
IF (@NextParentAccountID = @AccountID )
BEGIN
SET @IsCircular = 1
BREAK
END
END
IF @IsCircular = 1
BEGIN
select 'CircularReference' as 'ResponseCode'
END
Related Topics
What Does It Mean to Have Jobs with a Null Stop Date
Unique Constraint Controlled by a Bit Column
Sqlserver Case Expressions - Short Circuit Evaluation
How to Use SQL Server Compact Edition Ce on Mono
Find Most Common Elements in Array with a Group By
How to Perform the Same Aggregation on Every Column, Without Listing the Columns
Does Ms Access Suppress Primary Key Violations on Inserts
Concat Group by in Vertica SQL
Join Two Different Tables and Remove Duplicated Entries
Grant Access to Just One Schema in Postgresql
Date Calculation with Parameter in Ssis Is Not Giving the Correct Result
Is Order by and Row_Number() Deterministic
Insert Multiple Rows in One Table Based on Number in Another Table