At 05:04 PM 3/07/2003 -0700, you wrote:

> > The reason why I suggested it as the right one, is that SELECT SINGULAR
> > returns either one row or no rows. You don't get an exception because, if
> > the output evaluates to > 1 row, the SINGULAR requirement is not met, and
> > you get no rows back at all.
>
>When did they add in a SELECT SINGULAR syntax?

Oh woops, no, it's not available in the DISTINCT | FIRST etc. paradigm,
only as as an existential qualifier in a WHERE clause (where it has always
been).

>What I'm talking about has to do with which API call you use to execute the
>statement. If you are using the TIB_DSQL then you use a different API call
>than the TIB_Cursor uses. Which, when using it, implies a singular mode and
>no cursor has to be created and you get an exception if 0 or more than one
>record is returned.

OK, yes, that was understood. But where I was coming from is to find a
"bomb-proof" way of getting a "0 or one" restriction into the query
specification itself, so that the singleton restriction is never violated
and hence doesn't have to be handled via an exception.

It can be done this way:

SELECT A.* FROM ATABLE A
WHERE SINGULAR (
SELECT B.* FROM ATABLE B
WHERE B.criterion = whatever)

Helen