Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

T-SQL challenged

Back to Forum: Tech Off
  • User profile image

    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
       nowCCBaselineID int
       ,thenCCBaselineID int
      --populate temptable
      INSERT INTO @tempDIFFBL (nowCCBaselineID,thenCCBaselineID)
      SELECT @BaselineID
      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
      FROM @tempDIFFBL
      LEFT OUTER JOIN ccdiffbl
      ON nowCCBaselineID=ccdiffbl.currCCBaselineID
      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.

  • User profile image


  • User profile image

    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. Perplexed

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.