I changed all my checks to null tests:

(SELECT FIRST 1
1
FROM
CLAIMSPAIDREVERSED
WHERE
RXCLAIMNBR=COALESCE(NEW.RXCLAIMNBR,OLD.RXCLAIMNBR) AND
CLMSEQNBR=COALESCE(NEW.CLMSEQNBR,OLD.CLMSEQNBR) AND
CLAIMSTS=COALESCE(NEW.CLAIMSTS,OLD.CLAIMSTS) AND
(DECIMALQTY * COALESCE(NEW.QTY_CLAIM,OLD.QTY_CLAIM)) < 0
) IS NULL

I still don't know if the coalesce is needed, I don't know what value
the check uses for the test. I assume it's doing what I have under the
covers, but I haven't had time to create a test.

-----Original Message-----
From: Adam [mailto:s3057043@...]
Sent: Monday, January 24, 2005 6:04 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Check constraint error



Can you use the exists keyword in your check as per page 29/30 of the
LangRef.pdf, and rework that sql to do the sign check for you. IB_UDF
contains the sign function.

Otherwise, just use First 1 to calm firebirds nerves about the
singleton thing.





--- In firebird-support@yahoogroups.com, "Rick DeBay" <rdebay@r...>
wrote:

> 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

Yahoo! Groups Links