At 11:40 PM 17-11-02 +1030, you wrote:

>Hi Helen.
>
>
>Helen Borrie wrote:
> >
> > At 12:00 PM 17-11-02 +1030, you wrote:
> > >Bug in IBOQuery: Infinite occurrence of Error message "Multiple Rows in
> > >Singleton Fetch"
> >
> > Ahem, would you like to retitle that to something more apposite, like
> > "IBOQuery found my bugs"?
>
>There is no need for IBObjects to announce an error an infinite number of
>times.

IBO is reporting each occurrence of the error, not the same error multiple
times. The immediate cause of the error is that your KeyLinks say that
there will be one and only one occurrence of each ID. Your query returns
at least two for each (one for ParentID==Sire ID and one for ParentID=Dam
ID), hence the errors just keep on coming.

In your output set, you need at least ID and ParentID as
Keylinks. However, ParentID is not valid as a Keylink because it is not a
database column. This leaves you with the DB_KEY. This is what the editor
used, which explains why you were able to get a set there. You could try
adding DB_KEY to the KeyLinks and see whether this solves this particular
problem. Keep ID in the KeyLinks because, although it doesn't contribute
to the uniqueness, I see from another posting that you want to use it as a
search key.

>Once
>would suffice. It is irrelevant what errors I made, and tools should be
>able to handle an
>incorrect query with grace.

The problem is that IBO can't tell that the query is incorrect until the
server starts returning rows. At that point, it determines that it is
getting pairs of rows per KeyLinks and it barfs on each one. In your app
code, you could intercept the first occurrence of the error and avoid
having IBO process the rest of the set at all.

>I titled my message with 'Bug' to allow non-interested parties to avoid
>it. Is there another place to notify of bugs?

No, this is the right place. But it is better for you and for others if
you first determine whether the bug lies in IBO or in your code. I'm aware
that you have been struggling to understand KeyLinks but not all of our
list subscribers are aware of that.

There are other problems in this query, regarding your cursors and SQL
syntax, which affected the way IBO parsed it. The strange SQL presented by
IBO to the API was linked to these errors as well as to the bad KeyLinks.

1. Ambiguity
You are possibly unaware of the ambiguity problems that InterBase gets from
multi-table queries containing and operating on columns that are not fully
identified. You say that your query produced correct results in the
editor...however, without checking back each output row with the data, you
would not be certain that every row returned was a valid result. Firebird
is supposed to have fixed this, so if this query returned no ambiguity
errors, then we might be looking at a persistent bug in Firebird (if you
are using Fb, of course..)

2. Cursors
You wrote:

>Perhaps better would be to use A1, A2 for Sires and A3,A4 for Dams.

In this query, you are using only two cursors, where you need four. You
have really no way currently to be sure that your output is accurate,
because your Sire cursor is tripping over your Dam cursor and your two
subselects are tripping over each other. This fault also helped to cause
IBO to mis-parse your statement.

To resolve both 1 and 2, you need the following alterations.

SELECT A1.ID, A1.Name, A1.Sex, A1."SIre ID" AS ParentID FROM Animal A1
WHERE A1.Sex = 'M'
AND NOT EXISTS (SELECT A2."Sire ID" FROM Animal A2 WHERE A2."Sire ID" = A1.ID)
UNION
SELECT A3.ID, A3.Name, A3.Sex, A3."Dam ID" AS ParentID FROM Animal A3 WHERE
A3.Sex = 'F'
AND NOT EXISTS (SELECT A4."Dam ID" FROM Animal A4 WHERE A4."Dam ID" = A3.ID)

Next, the logic of your query. You said you were looking to return

>All the males that are not the sire of any animals.
>All the females that are not the dam of any animals.
>I want all animals that don't have any children and I want the fields
>"Sire ID" and "Dam ID" to appear in a single column as ParentID so that
>associated detail records can be displayed together.
>
>To satisfy the latter requirement I think the Sires need to be processed
>separately to the Dams and then join the two result-sets.

A UNION is not a join. It generates multiple output sets that are stacked
one on top of the other. Also, with DSQL, you cannot apply ORDER BY to the
combined output set but only to the individual subsets.

Did you notice that your two selects are not returning full information
about the parentage of the animals in ParentID? In the first set of the
union you will get the sires of the males and in the second, the dams of
the females.

If the sets are *really* as you want them, then consider placing your query
into a view or a selectable stored procedure. This way, you *can* use ID +
ParentID as KeyLinks; and, in the case of a view, you can include an Order
By clause on the output when you query it.

If you actually want both parents of each animal, it greatly simplifies
your query. You avoid the union; you can eliminate the need to use DB_KEY
for KeyLinks; and you can order the set in any way you like. Ask again in
this case.

regards,
Helen