rwheeler23
asked on
Violation Primary Key SQL Code even though I check for uniqueness
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is there a unique index in your table that would prevent you from inserting? What is the exact error message you are receiving?
ASKER
I knew I needed a fresh set of eyes. The problem was the TRXTYPE field. It is check against the wrong field. It should be checking against @TRXTYPE not @MASTERTYPE.
Thanks for pointing me in the right direction.
Thanks for pointing me in the right direction.
Great, good to know you solved the issue...
ASKER
CNTRLTYP will always be 0
APTVCHHM will always be '' (blank)
SPCLDIST will always be 0
So essentially the key fields are
BCHSOURC, TRANSNMBR, TRXTYPE and DSTSSQNUM
For each set of TRNSNNMBR records the only value that is changing is DSTSSQNM
I have looked at the data and it is different for each record. I may trim check for len(aptvchnm)=0 as opposed to ''. All of my tables do not allow NULLs for a field value.
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)