At 03:50 PM 31/12/2005 +0100, you wrote:

> > First, you might still get a multiple row exception, since there is
> nothing
> > to stop this query from returning multiple rows.
>
>That's not right. If IBO would use my complete where-part "where
>OMOAID=3", it would get only one row per fetch. The where-clause makes
>the query unambigous.

Only if it were impossible to have more than one row in the right-hand
stream where the foreign key matched the left stream's PK and OMOAID were 3.

> > The second is that you seem to be trying to use a lookup relationship to
> > implement an "after-the-event" master-detail relationship within the
> > logical structure of your tables. There is a risk here of putting the
> > dependencies inside the lookup set out of scope.
>
>Sorry, but why do you think, I'm doing that?

KeyLinks: PCID=ORPCID

The purpose of the lookup being to allow the value of ORPCID in the parent
table to be changed, thus linking the parent record to a different product.

As Stan suggested, it might well be a non-scalar condition in one of the
subqueries that is throwing the multiple rows error...

Note also that, in lookup relationships, Keylinks has to do two jobs. It
retains its job of identifying each row in the lookup set as unique. Its
additional job is to provide the one-to-many link to the polling key in the
parent set. For a joined set, the first condition usually requires
Keylinks, as a minimum, to comprise the primary key of *every* table
contributing to the set.

Although you don't have any fields in the set from the right-hand table's
stream -- which might lead you to believe that the primary key of the left
table's stream is adequate for the uniqueness role of Keylinks -- you have
a problem for the *other* role of Keylinks when the join condition fails to
return a matching record for the parent set's polling key ORPCID.

The WHERE clause of the lookup set is, of course, ignored, once the set is
being polled by the parent for the unique row that matches the lookup
key. For polling, the parent set replaces the entire WHERE clause
(SQLWhereItems) with its own, i.e., in this case, WHERE lookup_field =
polling_keyfield. That means that your WHERE clause is unstable. You can
stabilise it to some extent by replacing your lookup set with a view that
takes care of the set's limiting criteria and uses WITH CHECK OPTION.

Even so, a downstream change to the relationships in the right-hand
stream's set by another transaction (concurrent or not) has the potential
to put your lookup set out of scope. And you *still* have the potential
for an existing lookup key to have no match in the lookup set....

Helen