Thank you, Helen. Your reply is very detailed and quite informative.

Helen Borrie wrote:

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

IBObjects determine this as the immediate cause and informed me, but then it need not
continue processing.

> 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

No error message is provided when using ParentID

> 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

DB_KEY is not accepted. In the GSG it states that it is also necessary to set a flag,
namely KeyLinksAreDBKey, but I cannot find it as a property on the IBOQuery component, in
the GSG or in the IBObjects Help.

Specifying ID as a KeyLink does cause it to show up in the Order selection list, but it
has no effect.

> 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

Ok, I see. IB & FB don't allow cancelling a query.

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

How can I specify DB_KEY in this case?

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

Every row is a valid row.

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

Thanks.

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

Sorry. I used 'join' to mean 'combine into a single list', not the SQL 'JOIN'.

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

Thanks.

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

Yes, but there was no need to post here the other 2 UNIONS as well.

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

Thanks. I like this suggestion.

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

Thanks for your help.
--
Raymond Kennington
Programming Solutions
W2W Team B