I get the error 'multiple rows in singleton select' when I insert a row
into a table with the following check constraint. It isn't possible to
get back more than one row, as the three items in the where clause are
the primary key of the table.

/* make sure sign of QTY_CLAIM matches sign of DECIMALQTY */
ALTER TABLE CLAIM_INVOICEITEM ADD CONSTRAINT C_CLAIMINVOICEITEM_DECQTY
CHECK (

(SELECT
DECIMALQTY
FROM
CLAIMSPAIDREVERSED cpr
WHERE cpr.RXCLAIMNBR=RXCLAIMNBR AND cpr.CLMSEQNBR=CLMSEQNBR AND
cpr.CLAIMSTS=CLAIMSTS
) * QTY_CLAIM > 0

);

Rick DeBay
Senior Software Developer
RxStrategies.net