I have apparently failed with the following query. I need to be able to compare a ClearCase baseline with two predecessors; 1) the baseline used for the last successful build, and 2) the baseline used for the last XREV build.
The following query should find the predecessors and if the combo of the current and the previous do not already exist in the ccdiffbl table, insert them so a report can be generated at a later time. The 2 columns (ccdiffbl.currCCBaselineID, and ccdiffbl.prevCCBaselineID)
make up the PK for that table.
My problem is that for some reason, this query fails to identify that the PK already exists in some cases and attempts to insert duplicate rows. This is intermittent though and I have yet to figure out why this is the case.
--create temp table
DECLARE @tempDIFFBL TABLE
INSERT INTO @tempDIFFBL (nowCCBaselineID,thenCCBaselineID)
FROM componentbuild cb
INNER JOIN mapcomponentbuildtodeliverablebuild mcbdb
ON cb.ID = mcbdb.componentbuildID
WHERE cb.componentversionID = @CVID
AND ccbaselineID <> @BaselineID
--move records to ccdiffbl
INSERT INTO CCDiffBL (currCCBaselineID, prevCCBaselineID)
SELECT nowCCBaselineID, thenCCBaselineID
LEFT OUTER JOIN ccdiffbl
AND thenCCBaselineID = ccdiffbl.prevccbaselineID
WHERE ccdiffbl.currCCBAselineID IS null and
ccdiffbl.prevCCBAselineID IS null
I don't know if I gave enough information, or maybe too much... either way, any help would be greatly appreciated.
the section above is part of a much larger proc. The tran doesn't get committed until the very end and it is rolled back in the event of any error throughout.
The Proc is called once per build and is run serially by the calling loop for each build. The odd thing is for example, there were 9 builds of different products that used the same component so this proc was called 9 times with the same baseline.
The first one inserted the values correctly. The second and third saw that the values were already present and moved on. The next 6 all errors with a PK constraint as they attempted to insert the duplicate rows, which caused the overall proc to get rolledback.
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.