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. Once
would suffice. It is irrelevant what errors I made, and tools should be able to handle an
incorrect query with grace.

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

I observe that the word 'Preant' appears in the text as pasted, but not in the IBOQuery
that I was using, which is strange.

>
> I've spent about three hours on your query this afternoon, trying to
> explain why it can't produce a searchable dataset. I've arrived at several
> places, each of which tells me that you don't need to have either a union
> or correlated subqueries to get the dataset you need.
>
> So I'd like to start by asking you what *information* you want this dataset
> to generate. Would you please answer that in human English. For example,
> from this (SQL bugs and all):
>
> SELECT ID, Name, Sex, "SIre ID" AS ParentID FROM Animal A1
> WHERE Sex = 'M'
> AND NOT EXISTS (SELECT "Sire ID" FROM Animal A2 WHERE A2."Sire ID" = A1.ID)
> UNION
> SELECT ID, Name, Sex, "Dam ID" AS PreantID FROM Animal A1

This line copied and pasted with letters rearranged and should have read:

SELECT ID, Name, Sex, "Dam ID" AS ParentID FROM Animal A1

> WHERE Sex = 'F'
> AND NOT EXISTS (SELECT "Dam ID" FROM Animal A2 WHERE A2."Dam ID" = A1.ID)

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

>
> it appears that you are asking for two gi-knackerous Cartesian products here,
>
> "for each male animal XY in the table, show me all of the animals whose
> sire was not animal XY and for each female animal XX in the table, show me

All the males that are not the sire of any animals.

> all of the animals whose dam was not animal XX".

All the females that are not the dam of any animals.

>
> whereas what you are after is probably something a lot simpler, smaller,
> easier to get, and capable of being made to behave as a searchable dataset.

That would be good.

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.

The query gives the correct results in my test cases.

Thanks.

Raymond.
--
Raymond Kennington
Programming Solutions
W2W Team B