troubleshooting Question

Violation Primary Key SQL Code even though I check for uniqueness

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
5 Comments1 Solution401 ViewsLast Modified:
I have this simple SQL script that goes off and gets a receipt number and then looks for records in another table. If they are there based on the receipt number I insert parts of the record into a third table. I have an insert for this third table where I check for uniqueness before I insert a record. I only waht the record inserted if it is not there. If is is there do nothing. Every time it tries to insert a record even though it does exist and I get a primary key violation. Perhaps a fresh set of eyes will see what I am doing wrong in this code. I have been staring at it too long.

Just look for the INSERT statement. There is only one. The seven fields you see in the initial Select statement define the PK of the third table.
/* DECLARE CURSOR TO OBTAIN ALL JOB (CONTRACT) NUMBERS */
DECLARE GetJobsCurs CURSOR for
	SELECT rtrim(PACONTNUMBER)
	FROM PA01101
	ORDER BY PACONTNUMBER

DECLARE @PACONTNUMBER	CHAR(11)
DECLARE @POPRCTNM		CHAR(17)

DECLARE @BACHNUMB		CHAR(15)
DECLARE @BCHSOURC		CHAR(15)
DECLARE @VENDORID		CHAR(15)
DECLARE @SEQNUMBR		INTEGER
DECLARE @DOCAMNT		NUMERIC(19,5)
DECLARE @VNDDOCNM		CHAR(21)
DECLARE @RECEIPTDATE	DATETIME
DECLARE @CRDTAMNT		NUMERIC(19,5)
DECLARE @DEBITAMT		NUMERIC(19,5)
DECLARE @ACTNUMST		CHAR(64)
DECLARE @DISTTYPE		CHAR(30)
DECLARE @TRXSORCE		CHAR(13)
DECLARE @MASTERTYPE		CHAR(3)
DECLARE @TRXTYPE		CHAR(20)
DECLARE @DISTTRXTYPE	CHAR(20)
DECLARE @DISTSOURCE		CHAR(50)
DECLARE @DISTREF		CHAR(128)
DECLARE @DOCSOURCE		CHAR(32)

SET @MASTERTYPE  = 'VPH'
SET @TRXTYPE     = 'Purchase Order'
SET @DISTTRXTYPE = 'Other'
SET @DISTSOURCE  = 'PO Receiving Entry Header'
SET @DISTREF     = 'Imported from Historical Purchase Orders'
SET @DOCSOURCE   = 'Purchase Order Update'

/* Open the JOBS cursor and scan the project table and get all open jobs */
OPEN GetJobsCurs

FETCH NEXT FROM GetJobsCurs INTO @PACONTNUMBER
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
	DECLARE GetJobReceiptsCurs CURSOR for
		SELECT DISTINCT POPRCTNM
		FROM POP30310
		WHERE ProjNum=@PACONTNUMBER

	/* Open the JOBS Receipt cursor and scan for any receipts for this job */
	OPEN GetJobReceiptsCurs

	FETCH NEXT FROM GetJobReceiptsCurs INTO @POPRCTNM
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN

	DECLARE GetJobReceiptsDistCurs CURSOR for
		SELECT poh.BACHNUMB,poh.BCHSOURC,poh.VENDORID,pod.SEQNUMBR,poh.SUBTOTAL-poh.TRDISAMT+poh.FRTAMNT+poh.MISCAMNT+poh.TAXAMNT,poh.VNDDOCNM,poh.receiptdate,pod.CRDTAMNT,pod.DEBITAMT,gim.ACTNUMST,
		CASE pod.DISTTYPE
			WHEN 1 THEN 'PURCH'
			WHEN 2 THEN 'TRADE'
			WHEN 3 THEN 'FREIGHT'
			WHEN 4 THEN 'MISC'
			WHEN 5 THEN 'TAX'
			WHEN 6 THEN 'AVAIL'
			WHEN 7 THEN 'PAY'
			WHEN 8 THEN 'OTHER'
			WHEN 9 THEN 'ACCRUED'
			WHEN 10 THEN 'ROUND'
			WHEN 11 THEN 'OVHD'
			WHEN 12 THEN 'APP-OVHD'
			WHEN 13 THEN 'CASH'
			WHEN 14 THEN 'TAKEN'
			WHEN 15 THEN 'WORK IN PROGRESS'
			WHEN 16 THEN 'UNBILLED ACCOUNTS RECEIVABLE'
			WHEN 17 THEN 'COST OF GOODS SOLD'
			WHEN 18 THEN 'CONTRA ACCOUNT'
			WHEN 19 THEN 'PROJECT OVERHEAD'				
			WHEN 20 THEN 'UNBILLED PROJECT REVENUE'
		ELSE 'Unknown'
		END as DISTTYPE, poh.TRXSORCE
		FROM POP30390 pod,POP30300 poh,GL00100 gam,GL00105 gim
		WHERE pod.POPRCTNM=poh.POPRCTNM and pod.ACTINDX=gam.ACTINDX and pod.POPRCTNM=@POPRCTNM and gam.ACCATNUM BETWEEN 31 and 35 and pod.DISTTYPE NOT IN(15,17) and
			  gam.ACTINDX=gim.ACTINDX
	
		/* Open the job receipts distribution cursor and get all distributions that fit one of the five categories and is */
		/* not a Cost of Sales distribution */		
		OPEN GetJobReceiptsDistCurs

		FETCH NEXT FROM GetJobReceiptsDistCurs INTO @BACHNUMB,@BCHSOURC,@VENDORID,@SEQNUMBR,@DOCAMNT,@VNDDOCNM,@RECEIPTDATE,@CRDTAMNT,@DEBITAMT,@ACTNUMST,@DISTTYPE,@TRXSORCE
		WHILE ( @@FETCH_STATUS = 0 )
		BEGIN

			IF NOT EXISTS (SELECT * FROM [GSE].[dbo].[JOB_LINKER] WHERE BCHSOURC = @BCHSOURC AND TRANSNMBR=@POPRCTNM AND TRXTYPE=@MASTERTYPE AND DSTSQNUM=@SEQNUMBR AND CNTRLTYP=0 AND APTVCHNM='' AND SPCLDIST=0)
			BEGIN
				/* INSERT THE VALUES FOR COSTS FROM JOB LINKER INTO THE COST DETAIL TABLE */
				INSERT INTO [GSE].[dbo].[JOB_LINKER]
				(JOBNUMBER,BACHNUMB,BCHSOURC,TRANSNMBR,MASTERTYPE,MASTERID,DOCAMNT,TRXTYPE,DSTSQNUM,CNTRLTYP,APTVCHNM,SPCLDIST,DOCNUMBR,
				DOCDATE,ACTNUMST,CRDTAMNT,DEBITAMT,DISTTYPE,DISTTRXTYPE,DISTSOURCE,DISTREF,TRXSORCE,DOCSOURCE)
				VALUES
				(@PACONTNUMBER,@BACHNUMB,@BCHSOURC,@POPRCTNM,@MASTERTYPE,@VENDORID,@DOCAMNT,@TRXTYPE,@SEQNUMBR,0,'',0,@VNDDOCNM,@RECEIPTDATE,@ACTNUMST,@CRDTAMNT,@DEBITAMT,@DISTTYPE,'Other','PO Receiving Entry','Imported from historical PO transactions',@TRXSORCE,'Purchase Order Update')
			END

			FETCH NEXT FROM GetJobReceiptsDistCurs INTO @BACHNUMB,@BCHSOURC,@VENDORID,@SEQNUMBR,@DOCAMNT,@VNDDOCNM,@RECEIPTDATE,@CRDTAMNT,@DEBITAMT,@ACTNUMST,@DISTTYPE,@TRXSORCE
		END

		CLOSE GetJobReceiptsDistCurs
		DEALLOCATE GetJobReceiptsDistCurs

		FETCH NEXT FROM GetJobReceiptsCurs INTO @POPRCTNM
	END

	CLOSE GetJobReceiptsCurs
	DEALLOCATE GetJobReceiptsCurs

	/* GET NEXT JOB (PROJECT) */
	FETCH NEXT FROM GetJobsCurs INTO @PACONTNUMBER
END

CLOSE GetJobsCurs
DEALLOCATE GetJobsCurs
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros