Thanks. I'll use DISTINCT or FIRST 1 depending on my mood at the time.

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Monday, January 24, 2005 4:24 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Check constraint error


Rick DeBay wrote:

> 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?).

Because the SQL standards says that a singleton select - meaning a
select that's not a FOR SELECT in PSQL - returns an error if there is
more than one record. That sounds capricious and arbitrary, but in
fact, selecting a list of results and considering only the first is not
good practice and could easily lead to incorrect and inconsistent
answers. In this case, you probably know that there's only one value
for DECIMALQTY that meets your criteria - in which case SELECT DISTINCT
would work.

Regards,


Ann




Yahoo! Groups Links