The stored procedure P_CHC_PHARMACY_IU is supposed to update a record if
it already exists. Instead the unique key violation is not being
handled, but only if these check constraints are in place.
If the check constraints are removed it works fine.
If the record doesn't exist so the procedure does an insert, it works
fine.
In addition a second error is being thrown at the same time, before the
key violation.

no current record for fetch operation
violation of PRIMARY or UNIQUE KEY constraint "INTEG_116" on table
"CHC_PHARMACY"

RECREATE TABLE CHC_PHARMACY
(
CHC D_ACCOUNT NOT NULL,
PHARMACY D_NABPNUM NOT NULL,
SUBACCT D_SOLOMON_ACCT NOT NULL,
CHC_SEQ D_SOLOMON_SEQ ,
PHARM_SEQ D_SOLOMON_SEQ ,
DESCRIPTION VARCHAR( 32),
PRIMARY KEY (CHC, PHARMACY)
);

CREATE PROCEDURE P_CHC_PHARMACY_IU (
CHC VarChar(7),
PHARMACY Decimal(7,0),
SUBACCT VarChar(24),
DESCRIPTION VarChar(32))
AS
BEGIN
INSERT INTO
CHC_PHARMACY (
CHC,
PHARMACY,
SUBACCT,
DESCRIPTION)
VALUES (
:CHC,
:PHARMACY,
:SUBACCT,
:DESCRIPTION);
WHEN SQLCODE -803 DO
UPDATE
CHC_PHARMACY
SET
SUBACCT = :SUBACCT,
DESCRIPTION = :DESCRIPTION
WHERE
CHC = :CHC AND
PHARMACY = :PHARMACY;
END

ALTER TABLE CHC_PHARMACY ADD CONSTRAINT C_CHCPHARM_CHCSEQ
CHECK (
(
SELECT FIRST 1
1
FROM
CHC_PHARMACY chcph
JOIN PHARMACY p ON chcph.PHARMACY = p.NABPNUM
JOIN PHARM_BILL_GRP pbg ON pbg.ID = p.BILL_GRP
WHERE
pbg.ID = (
SELECT
BILL_GRP
FROM
PHARMACY
WHERE
NABPNUM = COALESCE(NEW.PHARMACY,OLD.PHARMACY) ) AND
chcph.CHC <> COALESCE(NEW.CHC,OLD.CHC) AND
chcph.CHC_SEQ = COALESCE(NEW.CHC_SEQ,OLD.CHC_SEQ)
)
IS NULL);

ALTER TABLE CHC_PHARMACY ADD CONSTRAINT C_CHCPHARM_PHARMSEQ
CHECK (
(
SELECT FIRST 1
1
FROM
CHC_PHARMACY chcph
JOIN CHC chc ON chcph.CHC = chc.CHC
JOIN GRANTEE g ON g.COMPANYID = chc.GRANTEE
WHERE
g.COMPANYID = (
SELECT
GRANTEE
FROM
CHC
WHERE
CHC.CHC = COALESCE(NEW.CHC,OLD.CHC) ) AND
chcph.PHARMACY <> COALESCE(NEW.PHARMACY,OLD.PHARMACY) AND
chcph.PHARM_SEQ = COALESCE(NEW.PHARM_SEQ,OLD.PHARM_SEQ)
) IS NULL);

Rick DeBay
Senior Software Developer
RxStrategies.net