Tech Off Thread

3 posts

T-SQL challenged

Back to Forum: Tech Off
  • User profile image
    austink

    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
      (
       nowCCBaselineID int
       ,thenCCBaselineID int
      )
      --populate temptable
      INSERT INTO @tempDIFFBL (nowCCBaselineID,thenCCBaselineID)
      SELECT @BaselineID
       ,cb.ccbaselineID
      FROM componentbuild cb
      INNER JOIN mapcomponentbuildtodeliverablebuild mcbdb
       ON cb.ID = mcbdb.componentbuildID
      WHERE cb.componentversionID = @CVID
      AND
       (  
        mcbdb.deliverablebuildID=dbo.fn_getPrevGoodBuildID(@DBID)
        OR
        mcbdb.deliverablebuildID=dbo.fn_getPrevXrevBuildID(@DBID)
       )
      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
    gswitz

    Commits?

  • User profile image
    austink

    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

Comments closed

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.