At 06:04 PM 30/12/2005 +0100, you wrote:

>I have a TIB_Query with the SQL-Statement
>
>select PCID,
>PCPAID,
>PCMPID,
>PCNR,
>PCNAME1,
>(select PANAME from PRODUCTAREAS where
> PAID=PRODUCTS.PCPAID) as PANAME,
>(select MPNAME from MAINPRODUCTGROUPS where
> MPID=PRODUCTS.PCMPID) as MPNAME,
>PCTARGETRELEASEDATE,
>PCLISTPRICE,
>PCRRP,
>PCDAYONE,
>PCMINORDERQUANTITY
>from PRODUCTS join ORDERITEMSTABS_MP on PCMPID=OMMPID
>where OMOAID=3
>and PCACTIVE=1
>
>and the KeyLinks
>
>PRODUCTS.PCID=ORPCID
>
>and the KeySource is assigned.
>This Query is used a Lookup-Query to select a product. When I open the
>query, I get the following error
>
>Multiple rows in singleton fetch
>
>Check KeyLinks and JoinLinks properties
>
>select PCID,
>PCPAID,
>PCMPID,
>PCNR,
>PCNAME1,
>(select PANAME from PRODUCTAREAS where
>PAID=PRODUCTS.PCPAID) as PANAME,
>(select MPNAME from MAINPRODUCTGROUPS where
>MPID=PRODUCTS.PCMPID) as MPNAME,
>PCTARGETRELEASEDATE,
>PCLISTPRICE,
>PCRRP,
>PCDAYONE,
>PCMINORDERQUANTITY
>from PRODUCTS join ORDERITEMSTABS_MP on PCMPID=OMMPID
>WHERE ( PRODUCTS.PCID=? /* BIND_0 */ )
>
>in the function TIB_BindingCursor.QuickFetch in IB_Components row 29675
>
>That's right, because without the where-part
>
>where OMOAID=3
>and PCACTIVE=1
>
>from the SQL-Statement, the result will contain double products. I can
>solve the problem with using distinct, but I wonder, why the where-part
>from my query is ignored in this case.

I think you have two problems here, not one:

1) You are trying to establish a KeyLink from a field in Products. That
can't work because Products is not the underlying table of the lookup dataset.
The IBO parser ignores your WHERE clause because it must calculate its
WHERE criteria from the KeyLink, which is wrong.

2) The entire query is ambiguous in several ways. You will need either
full table qualifiers for EVERYTHING or (better) table aliases for EVERYTHING.

So I suggest you do two things:

-- Fix up the SQL of the lookup query
-- Use unqualified field names for the KeyLink.

Try this:

select
pr.PCID,
pr.PCPAID,
pr.PCMPID,
pr.PCNR,
pr.PCNAME1,
(select pa.PANAME from PRODUCTAREAS pa
where pa.PAID=pr.PCPAID) as PANAME,
(select mpg.MPNAME from MAINPRODUCTGROUPS mpg
where mpg.MPID=pr.PCMPID) as MPNAME,
pr.PCTARGETRELEASEDATE,
pr.PCLISTPRICE,
pr.PCRRP,
pr.PCDAYONE,
pr.PCMINORDERQUANTITY
from PRODUCTS pr
join ORDERITEMSTABS_MP oit
on pr.PCMPID=oit.OMMPID
where
oit.OMOAID=3
and pr.PCACTIVE=1

For KeyLinks: PCID=ORPCID

There are a few caveats here.

First, you might still get a multiple row exception, since there is nothing
to stop this query from returning multiple rows. All it takes is more than
one row in which both the join criteria and the where criteria are
true. For a lookup relationship, the parent (Orders?) must point to one
and only one row in the lookup set. Multiple occurrences of product.pcid
in the lookup set will create an ambiguity that cannot be resolved in a
lookup relationship.

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.

Need I say that a lookup set that draws on dependencies *other* than the
lookup key has a high risk of being unstable...? For example, it's not
obvious what will happen if the lookup set does not return any row that the
parent can match....or if another transaction does something that changes
the scope of the dependencies...

This query has so much interdependency, it might be better to rethink the
lookup logic and stabilise your implementation by using a view for your
lookup set.

Helen