At 02:44 PM 22/11/2004 +1300, you wrote:

>Hi All
>
>I am struggling to get the normal (high) performance from the following SQL.
>It is designed to retrieve 1 record per order that has one or more active
>lines - a list of incomplete orders. It started as ...
>
>SELECT DISTINCT so.SupOrdrRef, OrderNo, OrderDate, so.Comment, DelAddr,
>PtdFlag, sEntityRef
>'FROM SupplyOrder so
>JOIN SupplyLine sl ON sl.SupordrRef = so.SupordrRef
>WHERE so.EntityRef = 8 AND sl.CancelFlag = 'F' AND sl.SupTranRef IS Null AND
>sl.PackSlpRef IS Null;

select
so.SupOrdrRef,
so.OrderNo,
so.OrderDate,
so.Comment,
so.DelAddr,
so.PtdFlag,
so.sEntityRef
FROM SupplyOrder so
where
so.EntityRef = 8
and exists (
select 1 from SupplyLine sl
where
sl.SupordrRef = so.SupordrRef
AND sl.CancelFlag = 'F'
AND sl.SupTranRef IS Null
AND sl.PackSlpRef IS Null)

./heLen