>>>>Yeah I spotted that ;-)
>>>>
>>>>Thanks for both of your responses, however I'm getting the same results :-(
>>>
>>>Can you post SYS(3054,11) output?
>>
>>Rushmore optimization level for table p: none
>>Rushmore optimization level for table rr: none
>>Rushmore optimization level for intermediate result: none
>>Joining intermediate result and table rr using index tag Referra5
>>Rushmore optimization level for table p: none
>>Rushmore optimization level for table r: none
>>Rushmore optimization level for intermediate result: none
>>Joining intermediate result and table p using index tag Client1
>>Joining intermediate result and table r using index tag Referra5
>>
>>>How many records are in each table?
>>
>>Person = 19650
>>Referral = 52186
>>
>>>Is query optimizable if you run it w/o WHERE caluse ?
>>
>>No.
>>
>>>Can you create an index on cl_dob field and run query again?
>>
>>There is already an index on cl_dob.
>>
>>Kev
>
>
>So the Filtering I was expecting on cl_dob probably is exchanged for the use of the index Client1 ?
>I'ld try to get to my result in single steps and time/optimize those - even if at first sight at least some operations can be made in a single step. Just typing along without too much thinking so check your needs...

>select ;
>      cl_ref as Ref_CL, min(EVL(re_date, {^9000-12-31})) as dMin, {..} as dGoMonth
>    from referral ;
>    where re_date<>{..} ; && Dunno if this clashes with your business case if only checked here - think about it
>    group by 1 ;
>    into cursor T_Border readwrite
>
>&& probably the worst place, but gives you a baseline to compare different approaches !
>replace all dGoMonth with  GOMONTH(dMin, -216) in T_Border
>
>select * ;
>  from person ;
>  join T_Border ;
>    on cl_ref = Ref_CL  and cl_dob > dGoMonth
>
>*-- or
>
>select * ;
>  from person ;
>  join T_Border ;
>    on cl_ref = Ref_CL
>    where cl_dob > dGoMonth
>
>*-- or
>
>select * ;
>  from person ;
>  join T_Border ;
>    on cl_ref = Ref_CL
>    where cl_dob > GOMONTH(dMin, -216)  && substract time for replace here <g>
>
>*-- or
>
>select * ;
>  from person ;
>  join T_Border ;
>    on cl_ref = Ref_CL
>    where cl_dob + 1 > dGoMonth +1  && surely rushmore turned off here !
>
>*-- or
>
>select *, dGoMonth ;
>  from person ;
>  join T_Border ;
>    on cl_ref = Ref_CL
>    having cl_dob > dGoMonth    & second step for second step - doesn' t look good, but try anyway
>
>

>
>this is a two step process - I hope I haven't overlooked a reason for your 3 Table join..

The main reason for a single piece of SQL was because I'm using .Net to access the data....

However, I could probably still apply your recommendation using ADO.Net.

Thanks
Kev