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