Changing the query to SELECT FIRST 1 DECIMALQTY fixed the problem, but I
don't understand why (I mean I know why, but why doesn't returning only
one row work, instead of explicitly returning only one row?).

Rick DeBay

-----Original Message-----
From: Rick DeBay
Sent: Monday, January 24, 2005 1:38 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Check constraint error


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