Subject Re: [firebird-support] Check constraint error
Author Ann W. Harrison
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.