>>>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<>{..} ;
group by 1 ;
into cursor T_Border readwrite
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
select * ;
from person ;
join T_Border ;
on cl_ref = Ref_CL
where cl_dob > dGoMonth
select * ;
from person ;
join T_Border ;
on cl_ref = Ref_CL
where cl_dob > GOMONTH(dMin, -216)
select * ;
from person ;
join T_Border ;
on cl_ref = Ref_CL
where cl_dob + 1 > dGoMonth +1
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..
HTH
thomas